多列多条件SQL查询：筛选满足两组行条件的WO记录

问题背景 #

现有数据表：

WO | PS | C
----------------
   12 | 1  | a
   12 | 2  | b
   12 | 2  | b
   12 | 2  | c
   13 | 1  | a

需求：筛选出所有满足以下条件的WO：该WO对应的记录中同时存在PS=1 且 C='a'的行，以及PS=2 且 C='b'的行；最终返回这些WO下符合上述两个条件组的记录（排除无关行，比如WO12中PS=2 且 C='c'的记录）。

你之前尝试的WHERE PS = 1 AND C = 'a' AND PS = 2 AND C = 'b'无法生效，原因很简单：同一行不可能同时满足PS=1和PS=2，而且这个条件没有从WO维度判断整体是否同时存在两种目标记录。

解决方案 #

这里提供两种常用的实现方式：

方式1：子查询筛选合法WO后关联

SELECT t.*
FROM 你的表名 t
INNER JOIN (
    -- 先筛选出同时包含两种目标记录的WO
    SELECT WO
    FROM 你的表名
    GROUP BY WO
    HAVING 
        SUM(CASE WHEN PS = 1 AND C = 'a' THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN PS = 2 AND C = 'b' THEN 1 ELSE 0 END) > 0
) valid_wos ON t.WO = valid_wos.WO
-- 再过滤出符合目标条件组的行
WHERE (t.PS = 1 AND t.C = 'a') OR (t.PS = 2 AND t.C = 'b')

方式2：窗口函数标记合法WO

WITH wo_validation AS (
    SELECT 
        *,
        -- 标记当前WO是否包含PS=1且C=a的记录
        MAX(CASE WHEN PS = 1 AND C = 'a' THEN 1 ELSE 0 END) OVER (PARTITION BY WO) AS has_target1,
        -- 标记当前WO是否包含PS=2且C=b的记录
        MAX(CASE WHEN PS = 2 AND C = 'b' THEN 1 ELSE 0 END) OVER (PARTITION BY WO) AS has_target2
    FROM 你的表名
)
SELECT WO, PS, C
FROM wo_validation
WHERE has_target1 = 1 
  AND has_target2 = 1
  AND ((PS = 1 AND C = 'a') OR (PS = 2 AND C = 'b'))

执行结果 #

两种方式都会返回预期结果：

WO | PS | C
----------------
   12 | 1  | a
   12 | 2  | b
   12 | 2  | b

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