- uses union all to merge query results when doing a comprehensive statistical function, but the result set obtained by the query is found to be overwritten
- sql:
SELECT
studyCenterName,
grade,
weiji,
done,
zong
FROM
(
SELECT
e5. NAME AS studyCenterName,
e4.grade AS grade,
count(e1.id) AS weiji,
0 AS done,
0 AS zong
FROM
ex_examination e1
LEFT JOIN ex_stage e2 ON e1.stage_id = e2.id
LEFT JOIN ex_student_course e3 ON e1.student_course_id = e3.id
LEFT JOIN ex_student e4 ON e3.student_id = e4.id
LEFT JOIN ex_study_center e5 ON e4.study_center_id = e5.id
WHERE
e1. STATUS = 1
GROUP BY
e5. NAME,
e4.grade
UNION ALL
SELECT
e5. NAME AS studyCenterName,
e4.grade AS grade,
0 AS weiji,
count(e1.id) AS done,
0 AS zong
FROM
ex_examination e1
LEFT JOIN ex_stage e2 ON e1.stage_id = e2.id
LEFT JOIN ex_student_course e3 ON e1.student_course_id = e3.id
LEFT JOIN ex_student e4 ON e3.student_id = e4.id
LEFT JOIN ex_study_center e5 ON e4.study_center_id = e5.id
WHERE
e1.end_exam_date < NOW()
AND e1.is_simulate = 0
GROUP BY
e5. NAME,
e4.grade
UNION ALL
SELECT
e5. NAME AS studyCenterName,
e4.grade AS grade,
0 AS weiji,
0 AS done,
count(e1.id) AS zong
FROM
ex_examination e1
LEFT JOIN ex_stage e2 ON e1.stage_id = e2.id
LEFT JOIN ex_student_course e3 ON e1.student_course_id = e3.id
LEFT JOIN ex_student e4 ON e3.student_id = e4.id
LEFT JOIN ex_study_center e5 ON e4.study_center_id = e5.id
GROUP BY
e5. NAME,
e4.grade
) a
GROUP BY
studyCenterName,
grade
ORDER BY
studyCenterNamequery result:
unionsql:
is there any way to solve this kind of problem