The problem was not found in the mysql nknown column in 'where clause' field

problem description: change 10001 to task.task_no

in the following statement 
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`

there is an error that mysql Unknown column "op_task.task_no" in" where clause" cannot find this task_no field

mysql version 5.6, find out table A, all data, when table A"s task_type=1, the same number of task_no in table B and table A"s task_no, otherwise, the same number of task_no in table C and table A"s task_no,

Table An is: 
CREATE TABLE `op_task` (

id int (10) unsigned NOT NULL AUTO_INCREMENT,
task_type tinyint (2) NOT NULL COMMENT "task type). 1 = daily task | 2 = growing task",
task_icon varchar (250) NOT NULL COMMENT "task icon",
task_no int (5) unsigned NOT NULL COMMENT" task number",
task_name varchar (100) NOT NULL COMMENT "task name",
task_point int (11) unsigned NOT NULL DEFAULT"0" COMMENT "reward points",
task_receive_times int (11) DEFAULT "1percent,
task_remark)
task_show_status int (1) unsigned DEFAULT"1" COMMENT "whether to display: 1-display, 2-do not display",
task_sort int (11) NOT NULL DEFAULT"0" COMMENT "sort, sort from big to small",
task_status tinyint (1) DEFAULT"1" COMMENT "status. 1 = normal | 0 = disabled",
PRIMARY KEY ( id ),
UNIQUE KEY IDX_OP_TASK_TASK_NO ( task_no )
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT=" daily task table"

Table B is: 
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 "task number",
finish_time int (11) unsigned NOT NULL COMMENT "completion time",
finish_date int (8) unsigned NOT NULL COMMENT "completion time yyyymmdd",
PRIMARY KEY ( id ),
KEY < KEY > KEY
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=" Daily Task completion record"

Table C is: 
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 "task number",
finish_time int (11) unsigned NOT NULL COMMENT "completion time",
finish_date int (8) unsigned NOT NULL COMMENT "completion time yyyymmdd",
PRIMARY KEY ( id ),
KEY < KEY > KEY
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=" growth task completion record"

Php mysql
Mar.18,2022

you can't get the information of the parent query column in the child query, so report an error. According to your requirements, you can use variables to achieve this function 

-- 
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`;

after reading your SQL, it is more reasonable to separate the two kinds of task_type and change CASE WHEN to UNION (for reference only): 

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

Previous: Why can you directly import? after npm install jquery in vue

Next: Mysql failed to start successfully, looking for a solution

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu