基于PRODUCT_ID的STATUS值生成STATUS_2派生列的SQL查询需求

原表数据 #

PRODUCT_ID  STATUS  STATUS_2
A1          N       N
A1          M       M
A1          M       M
A1          N       N
A2          N       [NULL]
A2          N       [NULL]

需求规则 #

• 针对每个PRODUCT_ID，只要该分组内存在任意一行的STATUS值为M，则该分组所有行的派生列STATUS_2均为M
• 若该分组内所有行的STATUS值均为N，则派生列STATUS_2为N

期望输出 #

PRODUCT_ID  STATUS  STATUS_2
A1          N       M
A1          M       M
A1          M       M
A1          N       M
A2          N       N
A2          N       N

实现SQL语句 #

方案一：窗口函数（简洁高效） #

利用窗口函数MAX()按PRODUCT_ID分组统计，由于字符排序中M优先级高于N，分组内只要有M，MAX(STATUS)结果即为M，全为N则结果为N，直接满足需求：

SELECT 
    PRODUCT_ID,
    STATUS,
    MAX(STATUS) OVER (PARTITION BY PRODUCT_ID) AS STATUS_2
FROM your_table_name;

方案二：子查询关联 #

先通过子查询统计每个PRODUCT_ID的最大STATUS值，再与原表关联获取派生列：

SELECT 
    t.PRODUCT_ID,
    t.STATUS,
    COALESCE(s.max_status, 'N') AS STATUS_2
FROM your_table_name t
LEFT JOIN (
    SELECT PRODUCT_ID, MAX(STATUS) AS max_status
    FROM your_table_name
    GROUP BY PRODUCT_ID
) s ON t.PRODUCT_ID = s.PRODUCT_ID;

注：将语句中的your_table_name替换为实际表名即可。

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