Oracle 18c SQL查询过滤后返回多余e_id的问题咨询

测试数据 #

CREATE TABLE test_tab (
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(20)
);

INSERT INTO test_tab VALUES(805,'SRC',NULL,'Email');
INSERT INTO test_tab VALUES(805,'SRC',NULL,'Test');
INSERT INTO test_tab VALUES(805,'TGT',NULL,null);
INSERT INTO test_tab VALUES(805,'TGT',128,'Email');
INSERT INTO test_tab VALUES(6122,'SRC',NULL,'Email');
INSERT INTO test_tab VALUES(6122,'TGT',NULL,'Email');
INSERT INTO test_tab VALUES(122,'SRC',128,null);
INSERT INTO test_tab VALUES(122,'TGT',128,null);

COMMIT;

运行环境：Oracle 18c

需求说明 #

报表只保留符合以下规则的e_id对应的记录：同一个e_id下的所有记录必须同时满足

• ref_id字段非空
• sys_other字段取值只能是'Email'或者null
  只要同一个e_id下有任意一条记录不满足上述条件，该e_id的所有记录全部排除，校验逻辑和sys_name字段无关。

规则示例 #

• e_id=122：符合要求，纳入结果。其下所有记录ref_id均为128非空，sys_other为null属于允许值
• e_id=6122：不符合要求，排除。其下所有记录ref_id均为null
• e_id=805：不符合要求，排除。其下不仅存在ref_id为null的记录，还有sys_other='Test'的非允许值记录

原有写法的错误原因 #

原查询语句：

SELECT e_id,ref_id,sys_other
FROM test_tab
WHERE ref_id IS NOT NULL
  AND (sys_other = 'Email' OR sys_other IS NULL)
GROUP BY e_id,ref_id,sys_other;

这个写法的问题是逐行过滤而非按e_id全组校验：WHERE条件只会把不满足条件的单条记录过滤掉，不会把存在违规记录的整个e_id都排除。805下存在一条ref_id=128、sys_other='Email'的记录满足WHERE条件，因此会被错误返回，达不到预期效果。

预期输出 #

+------+--------+-----------+
| E_ID | REF_ID | SYS_OTHER |
+------+--------+-----------+
|  122 |    128 | null      |
+------+--------+-----------+

正确实现方案 #

核心逻辑是先识别出所有完全符合规则的e_id，再返回这些e_id对应的记录即可，两种常用写法如下：

方案1：NOT EXISTS 写法（语义最直观） #

直接排除掉存在任意违规记录的e_id：

SELECT DISTINCT e_id, ref_id, sys_other
FROM test_tab t
WHERE NOT EXISTS (
    SELECT 1
    FROM test_tab t2
    WHERE t2.e_id = t.e_id
      AND (
          -- 存在ref_id为空的记录
          t2.ref_id IS NULL
          -- 存在sys_other既不是null也不是Email的记录
          OR (t2.sys_other IS NOT NULL AND t2.sys_other <> 'Email')
      )
);

方案2：聚合分组校验写法（适配所有Oracle版本） #

通过HAVING子句筛选出没有违规记录的e_id，再关联原表取数：

SELECT t.e_id, t.ref_id, t.sys_other
FROM test_tab t
JOIN (
    SELECT e_id
    FROM test_tab
    GROUP BY e_id
    HAVING 
      -- 组内没有ref_id为空的记录
      COUNT(CASE WHEN ref_id IS NULL THEN 1 END) = 0
      -- 组内没有sys_other不符合要求的记录
      AND COUNT(CASE WHEN sys_other IS NOT NULL AND sys_other <> 'Email' THEN 1 END) = 0
) valid_e ON t.e_id = valid_e.e_id
GROUP BY t.e_id, t.ref_id, t.sys_other;

两种写法执行结果完全一致，都只会返回e_id=122的对应记录，符合预期。

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