如何在MySQL 5.7中实现两张学生表的全外连接并按年月聚合？

基于以下两张学生数据表，在MySQL 5.7中实现全外连接（Full Outer Join），并按年份、月份进行数据聚合，得到指定的统计结果。

表结构与样本数据 #

表1：student_p（学生积分表） #

创建表语句

CREATE TABLE student_p (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    S_id INT UNSIGNED NOT NULL,
    Points DOUBLE NOT NULL,
    P_date DATE NOT NULL
);

插入样本数据

INSERT INTO student_p VALUES
    (50055, 3330, 45, '2023-11-30'),
    (50056,  332, 43, '2013-10-31'),
    (50057, 3330, 22, '2013-10-30');

数据展示

表2：student_act（学生活动记录表） #

创建表语句

CREATE TABLE student_act (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    s_id INT UNSIGNED NOT NULL,
    VIDEO_SCORE DOUBLE NOT NULL,
    EXERCISESCORE DOUBLE NOT NULL,
    DS INT NOT NULL,
    A_date DATE NOT NULL
);

插入样本数据

INSERT INTO student_act VALUES
    (2333, 233, 22.43,  233.4455, 23, '2023-11-30'),
    (2334, 235, 24.566,      232, 34, '2023-10-31'),
    ( 322, 678, 23,           45, 23, '2022-10-30'),
    ( 433,  45, 23,           23, 43, '2022-10-01');

数据展示

注：仅两张表的ID字段不可重复，其余数据均可重复。

预期聚合结果 #

按年份、月份分组后，需得到如下统计结果：

解决方案（MySQL 5.7） #

MySQL 5.7本身不支持FULL OUTER JOIN语法，因此需要通过**LEFT JOIN + RIGHT JOIN + UNION**的方式模拟全外连接，再对结果进行聚合计算。

完整SQL语句 #

WITH 
-- 先对student_p按年月聚合
p_agg AS (
    SELECT 
        YEAR(P_date) AS year,
        MONTH(P_date) AS month,
        SUM(Points) AS points
    FROM student_p
    GROUP BY YEAR(P_date), MONTH(P_date)
),
-- 对student_act按年月聚合
act_agg AS (
    SELECT 
        YEAR(A_date) AS year,
        MONTH(A_date) AS month,
        SUM(VIDEO_SCORE) AS VIDEO_SCORE,
        SUM(EXERCISESCORE) AS EXERCISESCORE,
        SUM(DS) AS DS
    FROM student_act
    GROUP BY YEAR(A_date), MONTH(A_date)
)
-- 模拟全外连接：LEFT JOIN + RIGHT JOIN 去重合并
SELECT 
    COALESCE(p.year, act.year) AS year,
    COALESCE(p.month, act.month) AS month,
    p.points,
    act.VIDEO_SCORE,
    act.EXERCISESCORE,
    act.DS
FROM p_agg p
LEFT JOIN act_agg act ON p.year = act.year AND p.month = act.month
UNION
SELECT 
    COALESCE(p.year, act.year) AS year,
    COALESCE(p.month, act.month) AS month,
    p.points,
    act.VIDEO_SCORE,
    act.EXERCISESCORE,
    act.DS
FROM p_agg p
RIGHT JOIN act_agg act ON p.year = act.year AND p.month = act.month
ORDER BY year DESC, month DESC;

逻辑说明 #

1. 预聚合子查询：先分别对两张表按年份、月份分组，计算各分组的聚合值（student_p求和Points，student_act求和VIDEO_SCORE、EXERCISESCORE、DS），减少后续连接的数据量。
2. 模拟全外连接：
   • LEFT JOIN保留p_agg中所有年月数据，匹配act_agg对应年月的聚合值，无匹配则为NULL。
   • RIGHT JOIN保留act_agg中所有年月数据，匹配p_agg对应年月的聚合值，无匹配则为NULL。
   • 使用UNION合并两个结果并自动去重，得到全外连接的效果。
3. 排序：最终结果按年份降序、月份降序排列，与预期结果顺序一致。

内容的提问来源于stack exchange，提问作者rob