基于四列将数据合并到单行展示的SQL实现问题

原始SQL语句 #

select Staff
    , case when Schedule = 1 then course end as period 1
    , case when Schedule = 2 then course end as period 2
    , case when Schedule = 3 then course end as period 3
from table 1
join 
table 2
on condition...
join table 3 
on condition
where so and so....
group by ...

当前查询结果 #

期望结果 #

解决方案 #

核心思路是通过窗口函数为同一员工的同时段课程分配分组序号，再按序号聚合合并非NULL值，避免冗余数据。

完整SQL实现（兼容多数主流数据库） #

WITH ranked_courses AS (
    SELECT 
        Staff AS "Staff Name",
        CASE WHEN Schedule = 1 THEN course END AS "Period 1",
        CASE WHEN Schedule = 2 THEN course END AS "Period 2",
        CASE WHEN Schedule = 3 THEN course END AS "Period 3",
        -- 为每个员工的同时段课程分配行号
        ROW_NUMBER() OVER (
            PARTITION BY Staff, Schedule 
            ORDER BY course
        ) AS rn
    FROM table1
    JOIN table2 ON table1.id = table2.staff_id -- 替换为实际关联条件
    JOIN table3 ON table2.id = table3.schedule_id -- 替换为实际关联条件
    WHERE so and so.... -- 保留原WHERE条件
    -- 过滤无有效数据的全NULL行
    AND (course IS NOT NULL OR Schedule IS NOT NULL)
)
SELECT 
    "Staff Name",
    MAX("Period 1") AS "Period 1",
    MAX("Period 2") AS "Period 2",
    MAX("Period 3") AS "Period 3"
FROM ranked_courses
GROUP BY "Staff Name", rn
ORDER BY "Staff Name", rn;

关键说明 #

1. 窗口函数分组：ROW_NUMBER() 会给每个员工的同一时段（Schedule）的课程分配唯一序号，比如Harry的Period2有两门课程，会分别得到rn=1和rn=2。
2. 聚合合并：按员工和序号分组后，用MAX()提取每个时段的非NULL值，序号相同的行自然合并，实现"同一行尽可能填充多时段非NULL值"的需求。
3. 过滤无效行：提前过滤全NULL的行，减少不必要的计算和结果冗余。

兼容无CTE的数据库（改用子查询） #

SELECT 
    "Staff Name",
    MAX("Period 1") AS "Period 1",
    MAX("Period 2") AS "Period 2",
    MAX("Period 3") AS "Period 3"
FROM (
    SELECT 
        Staff AS "Staff Name",
        CASE WHEN Schedule = 1 THEN course END AS "Period 1",
        CASE WHEN Schedule = 2 THEN course END AS "Period 2",
        CASE WHEN Schedule = 3 THEN course END AS "Period 3",
        ROW_NUMBER() OVER (
            PARTITION BY Staff, Schedule 
            ORDER BY course
        ) AS rn
    FROM table1
    JOIN table2 ON table1.id = table2.staff_id
    JOIN table3 ON table2.id = table3.schedule_id
    WHERE so and so....
    AND (course IS NOT NULL OR Schedule IS NOT NULL)
) AS ranked_courses
GROUP BY "Staff Name", rn
ORDER BY "Staff Name", rn;

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