记录一下 两个表合并展示的SQL,
1,表1:child_game_course_sub_chapter_comment
表2:user_program_course_sub_chapter_comment
2, 分组条件:creator_uid ,user_set_group_id,月份
建议项目中少用这么复杂的SQL,要么调整需求,要么另走脚本
SELECT
months,
creator_uid,
user_set_group_id,
sum(id_0) as id_1,
sum(id_1) as id_0
FROM
(
SELECT
c.months,
c.creator_uid,
c.user_set_group_id,
SUM(c.id_1) AS id_0,
c.id AS id_1
FROM
(
SELECT
a.id,
0 AS id_1,
a.creator_uid,
a.user_set_group_id,
DATE_FORMAT(a.actual_submit_time,'%Y%m') months
FROM
child_game_course_sub_chapter_comment a
WHERE a.is_commit = 1 and a.actual_submit_time IS NOT NULL ORDER BY a.id DESC
) c
GROUP BY
c.months,
c.creator_uid,
c.user_set_group_id
UNION ALL
SELECT
d.months,
d.creator_uid,
d.user_set_group_id,
d.id AS id_0,
SUM(d.id_0) AS id_1
FROM
(
SELECT
b.id,
0 AS id_0,
b.creator_uid,
b.user_set_group_id,
DATE_FORMAT(b.actual_submit_time,'%Y%m') months
FROM
user_program_course_sub_chapter_comment b
WHERE b.is_commit = 1 and b.actual_submit_time IS NOT NULL ORDER BY b.id DESC
) d
GROUP BY
d.months,
d.creator_uid,
d.user_set_group_id
) t
GROUP BY
t.months,creator_uid,user_set_group_id ORDER BY t.months DESC
运行结果: