代码之家 › 专栏 › 技术社区 › BFF

使用with子句消除具有空值的重复行

distinct-values common-table-expression distinct oracle sql

1

BFF · 技术社区 · 7 年前

with x as (--queries with multiple join tables, etc.)
select distinct * from x

输出如下:

Com_no   Company      Loc    Rewards
1         Mccin      India      50
1         Mccin      India
2         Rowle      China      18
3         Draxel     China      11
3         Draxel     China  
4         Robo       UK

 with x as (--queries with multiple join tables, etc.)
 select distinct * from x where Rewards is not null

当然,这是不对的,因为它也摆脱了 4 Robo UK

预期产出应为:

1         Mccin      India      50
2         Rowle      China      18
3         Draxel     China      11 
4         Robo       UK

2 回复 | 直到 7 年前

1

2

Ben 7 年前

问题是您调用这些行是重复的,但它们不是重复的。他们是不同的。所以您要做的是排除其中的行 Rewards

select distinct * 
from x a
where Rewards is not null 
or (Rewards is null and not exists (select 1 from x b where a.Com_no = b.Com_no 
    and b.Rewards is not null)

现在,您的Robo行仍将包括在内,因为x中没有一行Robo的奖励不为空,但其他公司的奖励为空的行将被排除在外,因为它们没有空行。

2

1

Gordon Linoff 7 年前

row_number() . 如果您只希望每个值有一个值 Com_no / Company Loc

select x.*
from (select x.*,
             row_number() over (partition by Com_no, Company, Loc order by Rewards nulls last) as seqnum
      from x
     ) x
where seqnum = 1;

select Com_no, Company, Loc, max(Rewards)
from x
group by Com_no, Company, Loc;