SQL查询需求：筛选排除行及包含行减排除行结果

表格结构与字段含义 #

• 表格字段：id、a_id、b_type、e_id、sp_id、br_type
• 字段取值说明：
  • br_type：仅为B或H
  • b_type：
    • EA：排除方A
    • EB：排除方B
    • IA：包含方A
    • IB：包含方B

原始表格数据 #

规则说明 #

• 若指定id、a_id、e_id对应的sp_id为null，则该e_id无排除项
• 若sp_id有值且b_type为EA/EB，则该sp_id为排除项（例如sp_id=1同时存在EA与IA，最终需排除该行）

需求变更 #

初始需求为筛选基于包含值确定的排除行，预期输出：

2024年6月13日更新需求：改为筛选所有包含行减去排除行的结果，规则为：

• 若某sp_id同时存在EA/EB与IA/IB，则该行不纳入结果
• 若EA/EB的sp_id为null，则无排除项

现有尝试代码 #

用户已写出查询排除行的SQL，希望扩展得到最终结果：

WITH    --  S a m p l e    D a t a
  tbl ( id, a_id, b_type, e_id, sp_id, br_type ) AS
    ( Select 65593, '65593_C',  'EA',   238,    null,   'B' Union All
      Select 65593, '65593_C',  'EB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'EA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '2',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '23',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '3',      'H'
    )
select distinct b1.ID
  ,b1.A_ID
  ,b1.B_TYPE
  ,b1.E_ID
  ,b1.SP_ID
  ,b1.BR_TYPE
from tbl b1
inner join tbl b2 on b1.ID = b2.id
                and b1.A_ID = b2.A_ID 
                and b1.E_ID = b2.E_ID 
                and b1.SP_ID = b2.SP_ID
where b1.B_TYPE in ('EA', 'EB')--, 'IA', 'IB')
  and (b1.SP_ID is not null )--and b1.SP_ID != '')

核心思路是先提取所有需要排除的(id, a_id, e_id, sp_id)组合，再从包含行中过滤掉这些组合。完整SQL如下：

WITH tbl ( id, a_id, b_type, e_id, sp_id, br_type ) AS
    ( Select 65593, '65593_C',  'EA',   238,    null,   'B' Union All
      Select 65593, '65593_C',  'EB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'EA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '2',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '23',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '3',      'H'
    ),
-- 生成需要排除的组合：存在EA/EB且sp_id非null的记录
exclude_set AS (
    SELECT DISTINCT id, a_id, e_id, sp_id
    FROM tbl
    WHERE b_type IN ('EA', 'EB') AND sp_id IS NOT NULL
)
-- 从包含行中排除掉exclude_set里的组合
SELECT t.id, t.a_id, t.b_type, t.e_id, t.sp_id, t.br_type
FROM tbl t
WHERE t.b_type IN ('IA', 'IB')
AND NOT EXISTS (
    SELECT 1
    FROM exclude_set es
    WHERE t.id = es.id
      AND t.a_id = es.a_id
      AND t.e_id = es.e_id
      AND t.sp_id = es.sp_id
);

执行结果 #

逻辑说明 #

• exclude_set CTE提取了所有需要排除的记录组合，仅包含有EA/EB标记且sp_id非空的记录
• 主查询只保留包含行（IA/IB），通过NOT EXISTS过滤掉在exclude_set中的组合，实现“包含行减排除行”的需求
• sp_id为null的EA记录不会进入exclude_set，因此对应的包含行不会被排除，符合规则要求

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