Oracle SQL：如何查询恰好匹配指定PGM值列表的行（视图场景）

针对需求——仅获取同时包含指定PGM值且无其他PGM值的行，且支持%（全匹配）和前缀匹配两种场景，以下是适配视图的可行方案：

核心思路 #

通过分组统计每个PN的PGM分布，同时满足两个关键条件：

1. 该PN下属于目标PGM列表（如'N','L'）的记录数，等于目标列表的长度（此处为2）——确保所有指定PGM都存在
2. 该PN下不存在任何不属于目标PGM列表的记录——确保没有额外的PGM值

优化后的SQL语句 #

SELECT al.*
FROM ALPHA al
WHERE al.PN IN (
    SELECT al2.PN
    FROM ALPHA al2
    WHERE TRIM(al2.NUM) = '2350'
      AND TRIM(al2.TEAM) = 'R2D2'
      AND TRIM(al2.PN) LIKE '%' -- 替换为实际匹配条件，比如前缀匹配时用'ABC%'
    GROUP BY al2.PN
    HAVING COUNT(DISTINCT TRIM(al2.PGM)) = 2 -- 目标PGM列表的长度
       AND SUM(CASE WHEN TRIM(al2.PGM) NOT IN ('N','L') THEN 1 ELSE 0 END) = 0 -- 无额外PGM值
       AND COUNT(DISTINCT CASE WHEN TRIM(al2.PGM) IN ('N','L') THEN TRIM(al2.PGM) END) = 2 -- 确保所有指定PGM都存在
)
AND TRIM(al.PGM) IN ('N','L')
ORDER BY al.PN ASC;

关键部分说明 #

1. 分组统计逻辑：

   • GROUP BY al2.PN：按PN分组，聚焦每个PN对应的PGM集合
   • COUNT(DISTINCT TRIM(al2.PGM)) = 2：确保该PN的PGM去重后总数与目标列表长度一致，结合下一个条件排除含额外PGM的情况
   • SUM(CASE...) = 0：统计不属于目标PGM的记录数，确保为0，彻底排除额外PGM值
   • COUNT(DISTINCT CASE...) = 2：单独统计目标PGM的去重数量，避免出现如两个'N'但无'L'的无效情况

2. 适配两种匹配场景：

   • 空匹配（全量）：保留TRIM(al2.PN) LIKE '%'
   • 前缀匹配：替换为TRIM(al2.PN) LIKE 'XXX%'（XXX为具体前缀内容）

3. 视图兼容性：
   所有操作均基于视图查询实现，无需修改底层表结构，完全适配生产环境的视图场景。

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