字段去除null合并一列的写法left join

如下图

with a as (select t.outflag,t.pk_corp, t.subjcode from bd_accsubj t where t.pk_accsubj in ('0001E11000000000050W','0001N510000000000OZW') )
 

SELECT distinct *
      FROM (SELECT you.subjcode,nvl(you.outflag, me.outflag) outflag, nvl(you.pk_corp, me.pk_corp )pk_corp
              FROM  a you
              left join a me
              on you.subjcode = me.subjcode)
    WHERE outflag is not null
     and pk_corp is not null

 ps：这里红色的subjcode需要指定表you，me都可以

下面的max用法也可以，原理是null字段是无限大，max的时候就忽略了

SELECT  subjcode, max(outflag),max(pk_corp )  FROM  a  group by subjcode

 

 

或者