SQL Server中INNER JOIN+CASE过滤状态数据未达预期问题求助

测试数据集 #

CREATE TABLE ServiceOrder (
  ServiceOrderId INTEGER ,
  EventStatus VARCHAR(100),
  Date DATE
);

INSERT INTO ServiceOrder VALUES (0001, 'status1A', CONVERT(date,'01.01.2022',104));
INSERT INTO ServiceOrder VALUES (0001, 'status1B', CONVERT(date,'12.01.2022',104));
INSERT INTO ServiceOrder VALUES (0001, 'status2', CONVERT(date,'01.02.2022',104));
INSERT INTO ServiceOrder VALUES (0001, 'status2', CONVERT(date,'22.02.2022',104));
INSERT INTO ServiceOrder VALUES (0001, 'status3', CONVERT(date,'01.03.2022',104));
INSERT INTO ServiceOrder VALUES (0001, 'status4', CONVERT(date,'01.04.2022',104));

INSERT INTO ServiceOrder VALUES (0002, 'status1A', CONVERT(date,'12.01.2022',104));
INSERT INTO ServiceOrder VALUES (0002, 'status1B', CONVERT(date,'01.01.2022',104));
INSERT INTO ServiceOrder VALUES (0002, 'status2', CONVERT(date,'01.02.2022',104));
INSERT INTO ServiceOrder VALUES (0002, 'status2', CONVERT(date,'22.02.2022',104));
INSERT INTO ServiceOrder VALUES (0002, 'status3', CONVERT(date,'01.03.2022',104));
INSERT INTO ServiceOrder VALUES (0002, 'status4', CONVERT(date,'12.01.2022',104));

需求说明 #

• 按ServiceOrderId分组，获取每个EventStatus的最小日期，同一状态同一订单仅显示一次
• 若同一ServiceOrderId同时包含status1A和status1B，仅保留日期更小的那个状态记录

原MySQL可行实现 #

以下语句在MySQL中可得到预期结果：

SELECT tableC.ServiceOrderId, tableC.EventStatus, MIN(tableC.Date) as tableCDate
FROM ServiceOrder as tableC, 
    (SELECT ServiceOrderId, MAX(tableADate) as tableBDate
    FROM (SELECT ServiceOrderId, EventStatus, MIN(Date) as tableADate
        FROM ServiceOrder
        GROUP BY ServiceOrderId, EventStatus
        ) as tableA
    WHERE EventStatus IN ('status1A', 'status1B')
    GROUP BY ServiceOrderId
    ) as tableB
where (CASE WHEN EventStatus IN ('status1A', 'status1B')
        THEN tableC.ServiceOrderId <> tableB.ServiceOrderId 
             and tableC.Date <> tableB.tableBDate 
        ELSE TRUE END
        )
GROUP BY tableC.ServiceOrderId, tableC.EventStatus

预期结果 #

ServiceOrderId  EventStatus tableCDate
1   status1A 2022-01-01
1   status2 2022-02-01
1   status3 2022-03-01
1   status4 2022-04-01
2   status1B 2022-01-01
2   status2 2022-02-01
2   status3 2022-03-01
2   status4 2022-01-12

SQL Server迁移后的问题 #

迁移到SQL Server后，修改的INNER JOIN+CASE语句无法正确过滤status1A和status1B，仍会同时显示两者：

SELECT tableC.ServiceOrderId, tableC.EventStatus, MIN(tableC.Date) as tableCDate
FROM ServiceOrder as tableC
INNER JOIN 
    (SELECT ServiceOrderId, MAX(tableADate) as tableBDate
    FROM (SELECT ServiceOrderId, EventStatus, MIN(Date) as tableADate
          FROM ServiceOrder
          GROUP BY ServiceOrderId, EventStatus) as tableA
    WHERE EventStatus IN ('status1A', 'status1B')
    GROUP BY ServiceOrderId
    ) as tableB
ON (CASE WHEN EventStatus IN ('status1A', 'status1B')
        AND tableC.ServiceOrderId <> tableB.ServiceOrderId 
        AND tableC.Date <> tableB.tableBDate 
    THEN 0
    ELSE 1 END
    )=1
GROUP BY tableC.ServiceOrderId, tableC.EventStatus

错误结果 #

ServiceOrderId  EventStatus tableCDate
1   status1A    2022-01-01
1   status1B    2022-01-12
1   status2 2022-02-01
1   status3 2022-03-01
1   status4 2022-04-01
2   status1A    2022-01-12
2   status1B    2022-01-01
2   status2 2022-02-01
2   status3 2022-03-01
2   status4 2022-01-12

正确的SQL Server解决方案 #

改用CTE结合窗口函数的方式实现，逻辑更清晰且适配SQL Server：

WITH StatusMinDates AS (
    -- 获取每个订单每个状态的最小日期
    SELECT ServiceOrderId, EventStatus, MIN(Date) AS MinDate
    FROM ServiceOrder
    GROUP BY ServiceOrderId, EventStatus
),
FilteredStatus1 AS (
    -- 对每个订单的status1A/status1B，仅保留日期最小的记录
    SELECT ServiceOrderId, EventStatus, MinDate
    FROM (
        SELECT 
            ServiceOrderId, 
            EventStatus, 
            MinDate,
            -- 按日期排序，取每个订单的第一条（日期最小）
            ROW_NUMBER() OVER (PARTITION BY ServiceOrderId ORDER BY MinDate) AS rn
        FROM StatusMinDates
        WHERE EventStatus IN ('status1A', 'status1B')
    ) t
    WHERE rn = 1
),
NonStatus1Records AS (
    -- 获取所有非status1A/status1B的记录
    SELECT ServiceOrderId, EventStatus, MinDate
    FROM StatusMinDates
    WHERE EventStatus NOT IN ('status1A', 'status1B')
)
-- 合并两类记录并排序
SELECT * FROM FilteredStatus1
UNION ALL
SELECT * FROM NonStatus1Records
ORDER BY ServiceOrderId, EventStatus;

执行结果 #

该语句会输出符合预期的结果，正确过滤掉每个订单中日期较大的status1A或status1B记录。

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