Mysql 合并两个查询结果 结果被覆盖

Question

1. 在做一个综合统计功能时使用了union all来进行合并查询结果 但是查询得到的结果集发现结果被覆盖了
2. 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
    studyCenterName

查询结果:

把union连接的三个sql单独执行的结果集:

请问有什么办法解决这类问题吗

Answer 1

不是union all的问题吧。
sql语句最后有group by语句， 相同的studyCenterName,grade只保留一条，去掉看是否你想要的结果。

另外，这个sql的写法，使用group by之后，在select语句中非group by的字段没用聚合函数，在myslq高版本或其他数据库会报错的。