如何用单条SQL语句为符合条件的Tracking_No所有行统一赋值

需找出所有存在满足条件行的Tracking_No，并为该Tracking_No对应的每一行分配对应值。当前使用CASE逻辑编写的SQL仅能为满足条件的行赋值，无法覆盖同Tracking_No的所有行，寻求简洁的单条SQL语句实现需求。

现有代码 #

select [Tracking_No] ,
CASE WHEN [Tracking_No] in
(Select distinct [Tracking_No] where ([Row_Value] like '%A%') Then '1'
WHEN [Tracking Number] in
(Select distinct [Tracking_No] where ([Row_Value] like '%B%') Then '2'
ELSE '' END as Description
from dwh.mytable

期望效果 #

解决方案 #

可通过以下两种简洁的SQL写法实现需求，确保同Tracking_No的所有行获得统一赋值：

方法1：子查询关联 #

SELECT 
    t.[Tracking_No],
    t.[Row_Value],
    COALESCE(d.[Description], '') AS Description
FROM dwh.mytable t
LEFT JOIN (
    SELECT 
        [Tracking_No],
        CASE 
            WHEN MAX(CASE WHEN [Row_Value] LIKE '%A%' THEN 1 ELSE 0 END) = 1 THEN '1'
            WHEN MAX(CASE WHEN [Row_Value] LIKE '%B%' THEN 1 ELSE 0 END) = 1 THEN '2'
            ELSE '' 
        END AS Description
    FROM dwh.mytable
    GROUP BY [Tracking_No]
) d ON t.[Tracking_No] = d.[Tracking_No]

方法2：窗口函数（更简洁） #

SELECT 
    [Tracking_No],
    [Row_Value],
    CASE 
        WHEN MAX(CASE WHEN [Row_Value] LIKE '%A%' THEN 1 ELSE 0 END) OVER (PARTITION BY [Tracking_No]) = 1 THEN '1'
        WHEN MAX(CASE WHEN [Row_Value] LIKE '%B%' THEN 1 ELSE 0 END) OVER (PARTITION BY [Tracking_No]) = 1 THEN '2'
        ELSE '' 
    END AS Description
FROM dwh.mytable

逻辑说明 #

• 优先判断每个Tracking_No下是否存在包含'A'的行，存在则为该Tracking_No的所有行赋值'1'；
• 若不存在包含'A'的行，再判断是否存在包含'B'的行，存在则赋值'2'；
• 两种情况都不满足时，赋值为空字符串。

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