SQL Server：多关联同一张表返回单条记录的实现方法

表结构与测试数据 #

staff表 #

CREATE TABLE staff
(
    [case_id] int, 
    [staff] varchar(11), 
    [stafftype] varchar(10)
);
    
INSERT INTO staff
    ([case_id], [staff], [stafftype])
VALUES
    (1, 'Daffy, Duck', 'Primary'),
    (1, 'Bugs, Bunny', 'Additional'),
    (1, 'Elmer, Fudd', 'Additional'),
    (2, 'Daffy, Duck', 'Primary'),
    (2, 'Bugs, Bunny', 'Additional');

cases表 #

CREATE TABLE cases
(
    [case_id] int, 
    [casedate] datetime, 
    [caselocation] varchar(4)
);
    
INSERT INTO cases
    ([case_id], [casedate], [caselocation])
VALUES
    (1, '2023-01-01 00:00:00', 'Home'),
    (2, '2023-01-03 00:00:00', 'Away');

需求 #

每个case返回单独一行记录，每个case仅存在1个Primary类型员工，最多存在2个Additional类型员工。

示例结果（case_id=1） #

解决方案 #

可以通过条件聚合结合窗口函数实现，以下是适用于SQL Server的查询语句：

SELECT 
    c.case_id,
    c.caselocation,
    MAX(CASE WHEN s.stafftype = 'Primary' THEN s.staff END) AS PrimaryStaff,
    MAX(CASE WHEN rn = 1 THEN s.staff END) AS AdditionalStaff1,
    MAX(CASE WHEN rn = 2 THEN s.staff END) AS AdditionalStaff2
FROM cases c
JOIN (
    SELECT 
        case_id,
        staff,
        stafftype,
        ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY staff) AS rn
    FROM staff
    WHERE stafftype = 'Additional'
) s ON c.case_id = s.case_id
GROUP BY c.case_id, c.caselocation
UNION ALL
-- 处理无Additional员工的case
SELECT 
    case_id,
    caselocation,
    (SELECT staff FROM staff s WHERE s.case_id = c.case_id AND s.stafftype = 'Primary') AS PrimaryStaff,
    NULL AS AdditionalStaff1,
    NULL AS AdditionalStaff2
FROM cases c
WHERE NOT EXISTS (SELECT 1 FROM staff s WHERE s.case_id = c.case_id AND s.stafftype = 'Additional')
ORDER BY case_id;

也可以使用PIVOT语法实现更简洁的写法：

WITH additional_staff AS (
    SELECT 
        case_id,
        staff,
        'AdditionalStaff' + CAST(ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY staff) AS VARCHAR) AS col_name
    FROM staff
    WHERE stafftype = 'Additional'
),
pivoted_additional AS (
    SELECT 
        case_id,
        AdditionalStaff1,
        AdditionalStaff2
    FROM additional_staff
    PIVOT (
        MAX(staff) FOR col_name IN (AdditionalStaff1, AdditionalStaff2)
    ) p
)
SELECT 
    c.case_id,
    c.caselocation,
    (SELECT staff FROM staff s WHERE s.case_id = c.case_id AND s.stafftype = 'Primary') AS PrimaryStaff,
    pa.AdditionalStaff1,
    pa.AdditionalStaff2
FROM cases c
LEFT JOIN pivoted_additional pa ON c.case_id = pa.case_id
ORDER BY case_id;

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