SQL Server中如何按不同条件二次连接表以获取目标查询结果？

原SQL的问题 #

• 语法错误：late, personal_id as late是错误写法，正确应为late.personal_id as late
• WHERE子句的过滤条件破坏了LEFT JOIN特性：若某个area没有early或late班次，early.shifttime = 'early'或late.shifttime = 'late'会直接过滤掉该area的记录，等同于INNER JOIN效果
• 未关联early和late的date字段，可能导致同一area下不同日期的班次错误关联，产生冗余数据

修正后的LEFT JOIN写法 #

将班次筛选和日期条件移至JOIN的ON子句中，同时确保同一日期的班次匹配：

SELECT
    AREAS.area,
    COALESCE(early.date, late.date) AS date,
    early.personal_id AS early,
    late.personal_id AS late
FROM 
    AREAS
LEFT JOIN 
    SHIFTS AS early 
    ON AREAS.area = early.area 
    AND early.shifttime = 'early'
    AND early.date = '2012-01-10'
LEFT JOIN 
    SHIFTS AS late 
    ON AREAS.area = late.area 
    AND late.shifttime = 'late'
    AND late.date = '2012-01-10'

使用COALESCE确保即使其中一个班次不存在，也能正确显示目标日期。

更简洁的PIVOT写法 #

需求属于将同一area、date下不同shifttime的personal_id转成列的场景，适合用SQL Server的PIVOT函数实现：

SELECT
    area,
    date,
    [early] AS early,
    [late] AS late
FROM (
    SELECT
        area,
        date,
        shifttime,
        personal_id
    FROM SHIFTS
    WHERE date = '2012-01-10'
) AS src
PIVOT (
    MAX(personal_id)
    FOR shifttime IN ([early], [late])
) AS pvt
RIGHT JOIN AREAS ON pvt.area = AREAS.area

通过RIGHT JOIN确保AREAS表中所有area都能被返回，即使该area在目标日期没有任何班次。

关于数据存储方式 #

当前的数据存储结构是合理的典型事实表+维度表设计，不需要调整，仅需通过正确的查询语句即可得到目标结果。

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