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

Question

问题描述:将下列语句中10001,更改为task.task_noSELECT`task`.*,(casetask.task_typewhen1then(selectcount(*)from(selectuidfromncd_user_daily_tasks_recordwheretask_no=10001groupbyuid)astemp1)else(selectcount(*)from(selectuidfromncd_user_development_tasks_recordwheretask_no=10001groupbyuid)astemp2)end)asnumber,(casetask.task_typewhen1then(selectcount(*)from(selectuidfromncd_user_daily_tasks_recordwheretask_no=10001)astemp1)else(selectcount(*)from(selectuidfromncd_user_development_tasks_recordwheretask_no=10001)astemp2)end)astotal_numberFROM`op_task``task`则出现mysqlUnknowncolumn'op_task.task_no'in'whereclause'找不到这个task_no字段的错误mysql版本为5.6,查出表A,所有数据,当表A的task_type=1时,统计表B中task_no和表A的task_no相同的数量,否则统计表C中task_no和表A的task_no相同的数量,表A为:CREATETABLE`op_task`(idint(10)unsignedNOTNULLAUTO_INCREMENT,task_typetinyint(2)NOTNULLCOMMENT'任务类型。1=每日任务|2=成长任务',task_iconvarchar(250)NOTNULLCOMMENT'任务icon',task_noint(5)unsignedNOTNULLCOMMENT'任务编号',task_namevarchar(100)NOTNULLCOMMENT'任务名称',task_pointint(11)unsignedNOTNULLDEFAULT'0'COMMENT'奖励积分',task_receive_timesint(11)DEFAULT'1',task_remarkvarchar(250)DEFAULTNULLCOMMENT'任务备注',task_show_statusint(1)unsignedDEFAULT'1'COMMENT'是否显示：1-显示，2-不显示',task_sortint(11)NOTNULLDEFAULT'0'COMMENT'排序，从大往小排序',task_statustinyint(1)DEFAULT'1'COMMENT'状态。1=正常|0=禁用',PRIMARYKEY(id),UNIQUEKEYIDX_OP_TASK_TASK_NO(task_no))ENGINE=InnoDBAUTO_INCREMENT=19DEFAULTCHARSET=utf8COMMENT='每日任务表'表B为:CREATETABLE`ncd_user_daily_tasks_record`(idbigint(20)unsignedNOTNULLAUTO_INCREMENT,uidint(11)unsignedNOTNULL,task_noint(5)unsignedNOTNULLCOMMENT'任务编号',finish_timeint(11)unsignedNOTNULLCOMMENT'完成时间',finish_dateint(8)unsignedNOTNULLCOMMENT'完成时间yyyymmdd',PRIMARYKEY(id),KEYIDX_NCD_USER_DAILY_TASKS_RECORD_TASK_NO(task_no),KEYIDX_NCD_USER_DAILY_TASKS_RECORD_FINISH_DATE_TASK_NO(finish_date,task_no)USINGBTREE,KEYIDX_NCD_USER_DAILY_TASKS_RECORD_UID_TASK_NO(uid,task_no)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=139DEFAULTCHARSET=utf8COMMENT='每日任务完成记录'表C为:CREATETABLE`ncd_user_development_tasks_record`(idbigint(20)unsignedNOTNULLAUTO_INCREMENT,uidint(11)unsignedNOTNULL,task_noint(5)unsignedNOTNULLCOMMENT'任务编号',finish_timeint(11)unsignedNOTNULLCOMMENT'完成时间',finish_dateint(8)unsignedNOTNULLCOMMENT'完成时间yyyymmdd',PRIMARYKEY(id),KEYIDX_NCD_USER_DEV_TASKS_TASK_NO(task_no),KEYIDX_NCD_USER_DEV_TASKS_UID_TASK_NO(uid,task_no)USINGBTREE,KEYIDX_NCD_USER_DEV_TASKS_FINISH_DATE_TASK_NO(finish_date,task_no)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=20DEFAULTCHARSET=utf8COMMENT='成长任务完成记录'

Answer 1

在子查询里面是获取不到父查询列信息的,所以报错,按照你的需求，可以用变量来实现这个功能
--定义变量
SET@task_task_no=0;
SELECT
--变量赋值
@task_task_no:=task_no,
`task`.*,(
CASEtask.task_type
WHEN1THEN
(
SELECT
count(*)
FROM
(
SELECT
uid
FROM
ncd_user_daily_tasks_record
WHERE
task_no=@task_task_no
GROUPBY
uid
)AStemp1
)
ELSE
(
SELECT
count(*)
FROM
(
SELECT
uid
FROM
ncd_user_development_tasks_record
WHERE
task_no=@task_task_no
GROUPBY
uid
)AStemp2
)
END
)ASnumber,
(
CASEtask.task_type
WHEN1THEN
(
SELECT
count(*)
FROM
(
SELECT
uid
FROM
ncd_user_daily_tasks_record
WHERE
task_no=@task_task_no
)AStemp1
)
ELSE
(
SELECT
count(*)
FROM
(
SELECT
uid
FROM
ncd_user_development_tasks_record
WHERE
task_no=@task_task_no
)AStemp2
)
END
)AStotal_number
FROM
`op_task``task`;
                            

Answer 2

看了你的SQL，感觉把两种task_type分开来处理更合理点，将CASEWHEN改成UNION（仅供参考）：
SELECTtask.*,number.total,number.uid_totalFROM
(
SELECT*FROMop_taskWHEREtask_type=1
)task,
(
SELECTtask_no,COUNT(*)total,COUNT(DISTINCTuid)uid_total
FROMncd_user_daily_tasks_recordGROUPBYtask_no
)number
WHEREtask.task_no=number.task_no
UNION
SELECTtask.*,number.total,number.uid_totalFROM
(
SELECT*FROMop_taskWHEREtask_type=2
)task,
(
SELECTtask_no,COUNT(*)total,COUNT(DISTINCTuid)uid_total
FROMncd_user_development_tasks_recordGROUPBYtask_no
)number
WHEREtask.task_no=number.task_no