SQL Server：同一请求号多Obj值校验及状态生成需求实现求助

现有表结构及数据 #

期望生成结果 #

规则说明 #

• 同一Request同时包含Obj=483和Obj=456时，结果为new
• 同一Request仅包含Obj=456且无Obj=483时，结果为WIP

实现代码 #

SELECT 
    T.Request,
    (CASE 
        WHEN (D.cn = 2 AND (obj = 483 OR obj = 456)) OR (D.cn = 1 AND obj = 456) THEN 'NEW'
        WHEN (D.cn = 1 AND obj = 483) THEN 'WIP'
        WHEN (D.cn = 2 AND (obj = 256 OR obj = 283)) OR (D.cn = 1 AND obj = 283) THEN 'Cancel'
        WHEN (D.cn = 1 AND obj = 256) THEN 'Cancel - WIP'
        ELSE 'NA' 
    END) AS [Result]
FROM table_name T
LEFT JOIN
(
    SELECT 
        Request,
        COUNT(DISTINCT CASE WHEN obj IN (483,456,283,256) THEN obj END) AS cn
    FROM table_name
    GROUP BY Request
) D
ON T.Request = D.Request
ORDER BY T.Request

代码说明 #

这段代码通过子查询统计每个Request下符合指定Obj值（483、456、283、256）的不同Obj数量，再通过CASE语句根据统计结果和当前行的Obj值判断对应状态：

• 当Request下有2个指定Obj且当前行是483或456，或者仅含1个指定Obj且为456时，返回NEW
• 当Request下仅含1个指定Obj且为483时，返回WIP
• 针对Obj=283和256的场景，也定义了Cancel和Cancel - WIP两种状态
• 其他未匹配场景返回NA

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