mysql nknown column in 'where clause'字段找不到问题

Question

问题描述:将下列语句中10001,更改为task.task_no

SELECT `task`.*,(case task.task_type when 1 then (select count(*) from (select uid from ncd_user_daily_tasks_record where task_no=10001 group by uid) as temp1) else (select count(*) from (select uid from ncd_user_development_tasks_record where task_no=10001 group by uid) as temp2) end)as number,(case task.task_type when 1 then (select count(*) from (select uid from ncd_user_daily_tasks_record where task_no=10001) as temp1) else (select count(*) from (select uid from ncd_user_development_tasks_record where task_no=10001) as temp2) end)as total_number FROM `op_task` `task`

则出现mysql Unknown column 'op_task.task_no' in 'where clause' 找不到这个task_no字段的错误

mysql版本为5.6,查出表A,所有数据,当表A的task_type=1时,统计表B中task_no和表A的task_no相同的数量,否则统计表C中task_no和表A的task_no相同的数量,

表A为:

CREATE TABLE `op_task` (

id int(10) unsigned NOT NULL AUTO_INCREMENT,
task_type tinyint(2) NOT NULL COMMENT '任务类型。1=每日任务|2=成长任务',
task_icon varchar(250) NOT NULL COMMENT '任务icon',
task_no int(5) unsigned NOT NULL COMMENT '任务编号',
task_name varchar(100) NOT NULL COMMENT '任务名称',
task_point int(11) unsigned NOT NULL DEFAULT '0' COMMENT '奖励积分',
task_receive_times int(11) DEFAULT '1',
task_remark varchar(250) DEFAULT NULL COMMENT '任务备注',
task_show_status int(1) unsigned DEFAULT '1' COMMENT '是否显示：1-显示，2-不显示',
task_sort int(11) NOT NULL DEFAULT '0' COMMENT '排序，从大往小排序',
task_status tinyint(1) DEFAULT '1' COMMENT '状态。1=正常|0=禁用',
PRIMARY KEY (id),
UNIQUE KEY IDX_OP_TASK_TASK_NO (task_no)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT='每日任务表'

表B为:

CREATE TABLE `ncd_user_daily_tasks_record` (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
uid int(11) unsigned NOT NULL,
task_no int(5) unsigned NOT NULL COMMENT '任务编号',
finish_time int(11) unsigned NOT NULL COMMENT '完成时间',
finish_date int(8) unsigned NOT NULL COMMENT '完成时间yyyymmdd',
PRIMARY KEY (id),
KEY IDX_NCD_USER_DAILY_TASKS_RECORD_TASK_NO (task_no),
KEY IDX_NCD_USER_DAILY_TASKS_RECORD_FINISH_DATE_TASK_NO (finish_date,task_no) USING BTREE,
KEY IDX_NCD_USER_DAILY_TASKS_RECORD_UID_TASK_NO (uid,task_no) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COMMENT='每日任务完成记录'

表C为:

CREATE TABLE `ncd_user_development_tasks_record` (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
uid int(11) unsigned NOT NULL,
task_no int(5) unsigned NOT NULL COMMENT '任务编号',
finish_time int(11) unsigned NOT NULL COMMENT '完成时间',
finish_date int(8) unsigned NOT NULL COMMENT '完成时间yyyymmdd',
PRIMARY KEY (id),
KEY IDX_NCD_USER_DEV_TASKS_TASK_NO (task_no),
KEY IDX_NCD_USER_DEV_TASKS_UID_TASK_NO (uid,task_no) USING BTREE,
KEY IDX_NCD_USER_DEV_TASKS_FINISH_DATE_TASK_NO (finish_date,task_no) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='成长任务完成记录'

Answer 1

在子查询里面是获取不到父查询列信息的, 所以报错, 按照你的需求，可以用变量来实现这个功能

-- 定义变量
SET @task_task_no=0;
SELECT
    -- 变量赋值
    @task_task_no := task_no,
    `task`.*, (
        CASE task.task_type
        WHEN 1 THEN
            (
                SELECT
                    count(*)
                FROM
                    (
                        SELECT
                            uid
                        FROM
                            ncd_user_daily_tasks_record
                        WHERE
                            task_no = @task_task_no
                        GROUP BY
                            uid
                    ) AS temp1
            )
        ELSE
            (
                SELECT
                    count(*)
                FROM
                    (
                        SELECT
                            uid
                        FROM
                            ncd_user_development_tasks_record
                        WHERE
                            task_no = @task_task_no
                        GROUP BY
                            uid
                    ) AS temp2
            )
        END
    ) AS number,
    (
        CASE task.task_type
        WHEN 1 THEN
            (
                SELECT
                    count(*)
                FROM
                    (
                        SELECT
                            uid
                        FROM
                            ncd_user_daily_tasks_record
                        WHERE
                            task_no = @task_task_no
                    ) AS temp1
            )
        ELSE
            (
                SELECT
                    count(*)
                FROM
                    (
                        SELECT
                            uid
                        FROM
                            ncd_user_development_tasks_record
                        WHERE
                            task_no = @task_task_no
                    ) AS temp2
            )
        END
    ) AS total_number
FROM
    `op_task` `task`;

Answer 2

看了你的 SQL，感觉把两种 task_type 分开来处理更合理点，将 CASE WHEN 改成 UNION（仅供参考）：

SELECT task.*, number.total, number.uid_total FROM
(
  SELECT * FROM op_task WHERE task_type = 1
) task,
(
  SELECT task_no, COUNT(*) total, COUNT(DISTINCT uid) uid_total 
  FROM ncd_user_daily_tasks_record GROUP BY task_no
) number
WHERE task.task_no = number.task_no

UNION

SELECT task.*, number.total, number.uid_total FROM
(
  SELECT * FROM op_task WHERE task_type = 2
) task,
(
  SELECT task_no, COUNT(*) total, COUNT(DISTINCT uid) uid_total 
  FROM ncd_user_development_tasks_record GROUP BY task_no
) number
WHERE task.task_no = number.task_no