sql如下，这个sql在正式库不可执行，测试库可执行，不存在缺少字段，正式库执行后提示Unknown column 't.comp_id' in 'on clause'，其实就是个简单的子查询引用父查询的字段，删掉最后两个复杂的子查询的话sql可以执行，足以证明单查t.comp_id是没问题的
SELECT t.province_id,
t.city_id,
t.train_type_id,
t.train_type_name,
t.comp_id,
t.salesman_id,
SUM(t.person_num) AS total_num,
SUM(t.complete_num_all) AS complete_num_all,
SUM(t.training_num) AS training_num,
DATE_ADD(CURDATE(), INTERVAL -1 DAY) AS statistics_date,
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), '%Y%m%d') AS statistics_date_str,
(SELECT COUNT(DISTINCT fnbf.bill_file_id) FROM fd_new_bill_project fnbp2
INNER JOIN fd_new_bill fnb2 ON fnbp2.bill_id = fnb2.bill_id
INNER JOIN fd_new_audit fna2 ON fnb2.bill_id = fna2.bill_id AND fna2.is_last = TRUE
INNER JOIN edu_project ep ON ep.project_id = fnbp2.project_id
AND fna2.audit_type != '90' AND fnbp2.comp_id = t.comp_id AND fnbp2.train_type_id = t.train_type_id
AND ep.salesman_id = t.salesman_id
AND fnbp2.area_id IN (SELECT child_id FROM sys_area_xref sax WHERE parent_id
= IFNULL(t.city_id, t.province_id)
)
INNER JOIN fd_new_bill_file fnbf ON fnb2.bill_id = fnbf.bill_id
AND fnbf.doc_type = '20') AS returnBillNum,
(SELECT COUNT(DISTINCT fnb2.bill_id) FROM fd_new_bill_project fnbp2
INNER JOIN fd_new_bill fnb2 ON fnbp2.bill_id = fnb2.bill_id
INNER JOIN fd_new_audit fna2 ON fnb2.bill_id = fna2.bill_id AND fna2.is_last = TRUE
INNER JOIN edu_project ep ON ep.project_id = fnbp2.project_id
AND fna2.audit_type != '90' AND fnbp2.comp_id = t.comp_id AND fnbp2.train_type_id = t.train_type_id
AND ep.salesman_id = t.salesman_id
AND fnbp2.area_id IN (SELECT child_id FROM sys_area_xref sax WHERE parent_id
= IFNULL(t.city_id, t.province_id)
) AND EXISTS (SELECT 1 FROM fd_new_audit fna3 WHERE fna3.bill_id = fnb2.bill_id AND fna3.audit_type = '50')) AS confirmBackNum
FROM (
SELECT ep.train_type_id,
ep.comp_id,
ep.salesman_id,
CASE sa.area_level
WHEN 1 THEN sa.area_id
WHEN 2 THEN sa.parent_area_id
ELSE
SUBSTR(sa.area_id_path, 1, INSTR(sa.area_id_path, ',') - 1) END AS province_id,
CASE sa.area_level
WHEN 1 THEN NULL
WHEN 2 THEN sa.area_id
ELSE
sa.parent_area_id END AS city_id,
gtt.train_type_name,
COUNT(1) AS person_num,
COUNT(IF(epuc.graduation_state = TRUE, 1, NULL)) AS complete_num_all,
COUNT(IF(epuc.graduation_state = TRUE, NULL, 1)) AS training_num
FROM edu_project ep
INNER JOIN sys_area sa ON ep.area_id = sa.area_id
INNER JOIN genre_train_type gtt ON ep.train_type_id = gtt.train_type_id
INNER JOIN edu_project_user_class epuc ON ep.project_id = epuc.project_id
WHERE ep.deleted = FALSE
AND epuc.deleted = FALSE
AND ep.audit_state = '30'
AND ep.start_date < CURDATE()
GROUP BY ep.project_id) t
GROUP BY t.province_id, t.city_id, t.train_type_id, t.comp_id, t.salesman_id;
忘了说了，测试环境是 8.0.22，正式是8.0.18，有四个小版本的差异
没人懂？
找到原因了，子查询引用父查询的字段，不写在连接条件中，放到where条件里就行了，比如
SELECT t.province_id,
t.city_id,
t.train_type_id,
t.train_type_name,
t.comp_id,
t.salesman_id,
SUM(t.person_num) AS total_num,
SUM(t.complete_num_all) AS complete_num_all,
SUM(t.training_num) AS training_num,
DATE_ADD(CURDATE(), INTERVAL -1 DAY) AS statistics_date,
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), '%Y%m%d') AS statistics_date_str
,
(SELECT COUNT(DISTINCT fnbf.bill_file_id) FROM fd_new_bill_project fnbp2
INNER JOIN fd_new_bill fnb2 ON fnbp2.bill_id = fnb2.bill_id
INNER JOIN fd_new_audit fna2 ON fnb2.bill_id = fna2.bill_id AND fna2.is_last = TRUE
INNER JOIN edu_project ep ON ep.project_id = fnbp2.project_id
AND fna2.audit_type != '90'
INNER JOIN fd_new_bill_file fnbf ON fnb2.bill_id = fnbf.bill_id
AND fnbf.doc_type = '20'
WHERE fnbp2.comp_id = t.comp_id AND fnbp2.train_type_id = t.train_type_id AND ep.salesman_id = t.salesman_id
AND fnbp2.area_id IN (SELECT child_id FROM sys_area_xref sax WHERE IF(t.city_id IS NULL, child_id, parent_id)
= IFNULL(t.city_id, t.province_id)
)
) AS returnBillNum,
(SELECT COUNT(DISTINCT fnb2.bill_id) FROM fd_new_bill_project fnbp2
INNER JOIN fd_new_bill fnb2 ON fnbp2.bill_id = fnb2.bill_id
INNER JOIN fd_new_audit fna2 ON fnb2.bill_id = fna2.bill_id AND fna2.is_last = TRUE
INNER JOIN edu_project ep ON ep.project_id = fnbp2.project_id
AND fna2.audit_type != '90'
where fnbp2.area_id IN (SELECT child_id FROM sys_area_xref sax WHERE IF(t.city_id IS NULL, child_id, parent_id)
= IFNULL(t.city_id, t.province_id)
) AND EXISTS (SELECT 1 FROM fd_new_audit fna3 WHERE fna3.bill_id = fnb2.bill_id AND fna3.audit_type = '50')
AND fnbp2.comp_id = t.comp_id AND fnbp2.train_type_id = t.train_type_id AND ep.salesman_id = t.salesman_id
) AS confirmBackNum
FROM (
SELECT ep.train_type_id,
ep.comp_id,
ep.salesman_id,
CASE sa.area_level
WHEN 1 THEN sa.area_id
WHEN 2 THEN sa.parent_area_id
ELSE
SUBSTR(sa.area_id_path, 1, INSTR(sa.area_id_path, ',') - 1) END AS province_id,
CASE sa.area_level
WHEN 1 THEN NULL
WHEN 2 THEN sa.area_id
ELSE
sa.parent_area_id END AS city_id,
gtt.train_type_name,
COUNT(1) AS person_num,
COUNT(IF(epuc.graduation_state = TRUE, 1, NULL)) AS complete_num_all,
COUNT(IF(epuc.graduation_state = TRUE, NULL, 1)) AS training_num
FROM edu_project ep
INNER JOIN sys_area sa ON ep.area_id = sa.area_id
INNER JOIN genre_train_type gtt ON ep.train_type_id = gtt.train_type_id
INNER JOIN edu_project_user_class epuc ON ep.project_id = epuc.project_id
WHERE ep.deleted = FALSE
AND epuc.deleted = FALSE
AND ep.audit_state = '30'
AND ep.start_date < CURDATE()
GROUP BY ep.project_id) t
GROUP BY t.province_id, t.city_id, t.train_type_id, t.comp_id, t.salesman_id;