DB2条件过滤SQL问题：按ID筛选时实现ABBREV优先级逻辑

原始数据 #

需求 #

• 筛选ID = 1时，仅返回ID=1且ABBREV='A'的记录（同一ID下ABBREV为'A'的记录优先级更高）
• 筛选ID = 2时，返回所有ID=2的记录（该ID下无'A'记录）

原SQL存在的问题 #

1. 仅针对ID=1编写逻辑，未覆盖ID=2的场景
2. 第二部分查询逻辑矛盾：筛选ABBREV='B'的同时，HAVING条件要求MAX(ABBREV)='A'，会导致该部分无结果返回
3. 仅返回ID字段，未返回需求中的ABBREV字段，不符合输出要求

修正后的SQL方案 #

通用版本（支持所有ID的筛选逻辑） #

SELECT ID, ABBREV
FROM (
    SELECT 
        ID, 
        ABBREV,
        -- 为每个ID分组内的记录按优先级排序：A类记录排第1位
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN ABBREV = 'A' THEN 0 ELSE 1 END) AS rn,
        -- 标记当前ID分组内是否存在A类记录
        MAX(CASE WHEN ABBREV = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_a
    FROM TABLE_X
    -- 可选：如果只需要筛选特定ID，添加 WHERE ID IN (1,2) 即可
) t
-- 核心筛选逻辑：有A则取A，无A则取全部
WHERE (has_a = 1 AND rn = 1) OR has_a = 0

针对单个ID的示例 #

筛选ID=1：

SELECT ID, ABBREV
FROM (
    SELECT 
        ID, 
        ABBREV,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN ABBREV = 'A' THEN 0 ELSE 1 END) AS rn,
        MAX(CASE WHEN ABBREV = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_a
    FROM TABLE_X
    WHERE ID = 1
) t
WHERE (has_a = 1 AND rn = 1) OR has_a = 0

筛选ID=2：

SELECT ID, ABBREV
FROM (
    SELECT 
        ID, 
        ABBREV,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN ABBREV = 'A' THEN 0 ELSE 1 END) AS rn,
        MAX(CASE WHEN ABBREV = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_a
    FROM TABLE_X
    WHERE ID = 2
) t
WHERE (has_a = 1 AND rn = 1) OR has_a = 0

逻辑说明 #

• 内层子查询通过窗口函数完成两个关键标记：
  1. 用ROW_NUMBER()给每个ID下的记录排序，确保A类记录排在最前面
  2. 用MAX() OVER()统计每个ID是否存在A类记录
• 外层根据标记筛选结果：如果ID存在A类记录，只保留排序第一的A类记录；如果没有A类记录，则保留该ID的所有记录

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