MySQL多表JOIN查询问题：正确关联考勤计划与实际数据

数据表结构 #

attendance_schedules（考勤日程表） #

users（用户表） #

attendance_planned（计划考勤关联表） #

attendance_actual（实际考勤关联表） #

原查询问题 #

原SQL直接多次LEFT JOIN关联所有表，导致同一日期下的计划用户和实际用户形成笛卡尔积，出现大量重复数据，无法得到预期的对应关系。

修正后的SQL语句 #

WITH planned_data AS (
    SELECT 
        s.date,
        u.name AS planning_name,
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY u.id) AS rn
    FROM attendance_schedules s
    LEFT JOIN attendance_planned p ON p.attendance_schedule_id = s.id
    LEFT JOIN users u ON u.id = p.user_id
),
actual_data AS (
    SELECT 
        s.date,
        u.name AS actual_name,
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY u.id) AS rn
    FROM attendance_schedules s
    LEFT JOIN attendance_actual a ON a.attendance_schedule_id = s.id
    LEFT JOIN users u ON u.id = a.user_id
)
SELECT 
    COALESCE(p.date, a.date) AS Date,
    p.planning_name,
    a.actual_name
FROM planned_data p
FULL JOIN actual_data a 
    ON p.date = a.date AND p.rn = a.rn
ORDER BY Date, COALESCE(p.rn, a.rn);

逻辑说明 #

1. 先用两个CTE分别处理计划和实际考勤数据：
   • planned_data：按考勤日程分组，给每个计划用户分配一个序号rn
   • actual_data：同样按考勤日程分组，给每个实际用户分配序号rn
2. 用FULL JOIN（MySQL 8.0+支持）按日期和序号关联两组数据，保留所有计划和实际的记录，缺失部分自动补NULL
3. 最后按日期和序号排序，得到符合预期的结果

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