SQL中按类别匹配：存在AB时为整类填充1的实现需求

原测试代码 #

with Test as
( select 'A' as Cat, 'AB' as Typ from dual
  union 
select 'A' as Cat, 'AC' as Typ from dual
  union 
select 'A' as Cat, 'AE' as Typ from dual
  union
  select 'B' as Cat, 'AC' as Typ from dual
  union 
select 'C' as Cat, 'AB' as Typ from dual
  UNION 
SELECT 'C' as Cat, 'AE' AS Typ from dual) 

select  (case when Typ = 'AB' then 1 else 0 end) as N,
  (case when Typ = 'AB' then row_number() over (partition by Cat order by Cat asc) end) as r,
   Cat, Typ from Test;

当前输出问题 #

当前查询的N列仅在Typ='AB'的行显示1，同类别下其他行显示0，无法满足“类别内只要有AB记录，所有行N都为1”的需求。

修改后的SQL #

通过窗口函数按类别分组计算标记值，实现需求：

with Test as
( select 'A' as Cat, 'AB' as Typ from dual
  union 
select 'A' as Cat, 'AC' as Typ from dual
  union 
select 'A' as Cat, 'AE' as Typ from dual
  union
  select 'B' as Cat, 'AC' as Typ from dual
  union 
select 'C' as Cat, 'AB' as Typ from dual
  UNION 
SELECT 'C' as Cat, 'AE' AS Typ from dual) 

select  
  MAX(case when Typ = 'AB' then 1 else 0 end) over (partition by Cat) as N,
  (case when Typ = 'AB' then row_number() over (partition by Cat order by Cat asc) end) as r,
  Cat, Typ 
from Test;

逻辑说明 #

• MAX(case when Typ='AB' then 1 else 0 end) over (partition by Cat)：按Cat分组后，只要组内存在Typ='AB'的记录，MAX计算结果就是1，组内所有行都会继承这个值；若组内无AB记录，结果为0。
• 原r列的逻辑保持不变，仅调整N列的计算方式。

期望输出示例 #

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