基于其他提取行替换NULL的SQL

如果我理解正确,你需要这个:

select div, ref, invent_status, 
       case when invent_status is null and 
                 count(case when invent_status = 'A' then 1 end) over () = 0 
            then 'A' 
            else invent_status 
       end as new_status
  from t_ref 
  order by invent_status nulls first;

^demo

条件分析函数 count(case when invent_status = 'A' then 1 end) over () 检查是否有 A 在你的桌子上。如果“否”,并且当前状态为空,则替换为 A

样品表:

create table NS_11(
div int ,ref  varchar(10),INVENT_STATUS varchar(1));

insert into NS_11(div,ref) values(1,'REF001XT');
insert into NS_11 values(1,'REF002XT','A');
insert into NS_11 values(1,'REF003XT','B');
insert into NS_11 values(1,'REF004XT','C');
insert into NS_11(div,ref) values(1,'REF005XT');
insert into NS_11(div,ref) values(1,'REF006XT');
select * from  NS_11;

select div,ref,nvl(INVENT_STATUS,'A') from(select div,ref,INVENT_STATUS from NS_11 
minus select div,ref,INVENT_STATUS from NS_11  where rownum<=1 )
union all
select div,ref,INVENT_STATUS from NS_11 where rownum<=1;

 1  REF002XT    A
 1  REF003XT    B
 1  REF004XT    C
 1  REF005XT    A
 1  REF006XT    A
 1  REF001XT    (null)

select div,ref,INVENT_STATUS from T_REF where rownum<=1 
union
select div,ref,nvl(INVENT_STATUS,'A') from(select div,ref,INVENT_STATUS from T_REF 
minus select div,ref,INVENT_STATUS from T_REF  where rownum<=1 );

select DIV, REF,
       case
           when (INVENT_STATUS is null) then 'A'
           when INVENT_STATUS = 'A' then null
           when INVENT_STATUS != 'A' then INVENT_STATUS
       else INVENT_STATUS
       end as INVENT_STATUS
from
    t_ref
order by INVENT_STATUS nulls first;