case when where 显示没有该列 mysql_mysql nknown column in 'where clause'字段找不到问题汗血宝马...

问题描述:将下列语句中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=’成长任务完成记录’