Oracle SQL查询需求：仅获取ind_id和effect_from_dat均为null的特定记录

需要从Oracle数据表中筛选出所有行的ind_id和effect_from_dat均为null的ornnn_id对应的记录，比如样例中的ornnn_id=12345的行。

当前使用的查询语句：

select ornnn_id, ind_id, sl_effect_from_dat from table 
where sl_ind is null and sl_effect_from_dat is null

会返回ornnn_id=3574953的null行，但该id同时存在ind_id和effect_from_dat非null的记录，不符合需求，需要修正查询。

样例数据表 #

ornnn_id   ind_id   effect_from_dat
3574953     null    null
3574953     null    null
3574953     null    null    
3574953     null    null    
3574953     null    null
3574953     null    null
3574953     null    null
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
12345      null     null
12345      null     null
1212       3        08-AUG-2003 10.20.10
1212       3        08-AUG-2003 10.20.10

解决方案 #

方法1：使用NOT EXISTS子查询 #

通过子查询找出存在ind_id或effect_from_dat不为null的ornnn_id，然后在主查询中排除这些id，同时保留ind_id和effect_from_dat均为null的行：

select t.ornnn_id, t.ind_id, t.effect_from_dat
from your_table t
where t.ind_id is null 
  and t.effect_from_dat is null
  and not exists (
    select 1 
    from your_table t2 
    where t2.ornnn_id = t.ornnn_id 
      and (t2.ind_id is not null or t2.effect_from_dat is not null)
  );

方法2：使用分组+HAVING子句 #

先按ornnn_id分组，筛选出所有行的ind_id和effect_from_dat都为null的id，再关联原表获取对应记录：

select t.ornnn_id, t.ind_id, t.effect_from_dat
from your_table t
join (
  select ornnn_id
  from your_table
  group by ornnn_id
  having count(case when ind_id is not null or effect_from_dat is not null then 1 end) = 0
) valid_ids on t.ornnn_id = valid_ids.ornnn_id;

方法3：使用窗口函数 #

通过窗口函数标记每个ornnn_id是否存在非null的行，再筛选标记为0的记录：

select ornnn_id, ind_id, effect_from_dat
from (
  select 
    t.*,
    max(case when ind_id is not null or effect_from_dat is not null then 1 else 0 end) 
      over (partition by ornnn_id) as has_non_null
  from your_table t
)
where has_non_null = 0;

注意：将上述代码中的your_table替换为实际表名，字段名需与实际表字段一致（原查询中使用了sl_ind和sl_effect_from_dat，需确认是否为笔误，样例数据中字段是ind_id和effect_from_dat）

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