oracle 不同的字段利用union all弄在了同一个字段,其中一个字段为空,怎么不让为空的这一行显示出来

三火一木燊 2018-10-10 08:06:56
select
case when xxxxx then 'data1' end as discrepancy01
case when xxxxx then '' end as discrepancy02
from table

select
discrepancy01 as discrepancy
from po_data

union all

select
discrepancy02 as discrepancy
from po_data

sql类似就是这样,用了下面那种union all之后会有其中一个为空的也会单独显示一行,有没有什么办法不让为空的这行显示出来
...全文
833 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
nayi_224 2018-10-11
  • 打赏
  • 举报
回复
现在一看,发现楼主的例子不充分啊。对于这些样本数据,我至少能用3种完全不同的逻辑得出一样的结果。
卖水果的net 2018-10-11
  • 打赏
  • 举报
回复
引用 8 楼 nayi_224 的回复:
对于这样的数据 15 15 55 15 15 56 只会返回 15 15 55

-- 我就知道,你会有这样的数据 ,简单的修改一下就可以了
SQL> create table test(a int, b int, d int);
Table created
SQL> begin
  2      insert into test values(12, 12, 22);
  3      insert into test values(12, 12, null);
  4      insert into test values(13, 13, null);
  5      insert into test values(13, 13, 33);
  6      insert into test values(14, 14, null);
  7      insert into test values(15, 15, 55);
  8      insert into test values(15, 15, 56);
  9  end;
 10  /
PL/SQL procedure successfully completed
SQL> set null NULL;
Cannot SET NULL
SQL> col a format 99999;
SQL> col b format 99999;
SQL> col d format 99999;
SQL> with m as (
  2    select t.*,
  3    row_number() over(partition by a order by d) rn  from test t
  4  )
  5  select a, b, d from m
  6  where rn = 1 or d is not null;
    A     B     D
----- ----- -----
   12    12    22
   13    13    33
   14    14 
   15    15    55
   15    15    56
SQL> drop table test purge;
Table dropped

SQL> 
nayi_224 2018-10-11
  • 打赏
  • 举报
回复
看着union有些不爽,去掉了
with tab1 as (
select 1 a, 1 b, 9 c from dual union all
select 1 a, 1 b, 8 c from dual union all
select 1 a, 2 b, null c from dual union all
select 1 a, 2 b, 9 c from dual union all
select 1 a, 3 b, null c from dual union all
select 1 a, 3 b, null c from dual union all
select 1 a, 4 b, null c from dual 
)
, tab2 as (
select t1.a,
       t1.b,
       t1.c,
       row_number() over(partition by t1.a, t1.b order by t1.c) rn,
       count(1) over(partition by t1.a, t1.b) cot,
       count(t1.c) over(partition by t1.a, t1.b) cot_null
  from tab1 t1
)
select*from tab2 t1
where 1 = 1
  and (cot_null = cot or (cot_null != cot and rn = 1))
;
nayi_224 2018-10-11
  • 打赏
  • 举报
回复
引用 7 楼 wmxcn2000 的回复:

SQL> create table test(a int, b int, d int);
Table created
SQL> begin
  2      insert into test values(12, 12, 22);
  3      insert into test values(12, 12, null);
  4      insert into test values(13, 13, null);
  5      insert into test values(13, 13, 33);
  6      insert into test values(14, 14, null);
  7      insert into test values(14, 14, null);
  8  end;
  9  /
PL/SQL procedure successfully completed

SQL> with m as (
  2  select t.*, row_number() over(partition by a order by d) rn  from test t
  3  )
  4  select a, b, d from m where rn = 1;
    A     B     D
----- ----- -----
   12    12    22
   13    13    33
   14    14 
SQL> drop table test purge;
Table dropped

SQL> 
对于这样的数据 15 15 55 15 15 56 只会返回 15 15 55
卖水果的net 2018-10-11
  • 打赏
  • 举报
回复

SQL> create table test(a int, b int, d int);
Table created
SQL> begin
  2      insert into test values(12, 12, 22);
  3      insert into test values(12, 12, null);
  4      insert into test values(13, 13, null);
  5      insert into test values(13, 13, 33);
  6      insert into test values(14, 14, null);
  7      insert into test values(14, 14, null);
  8  end;
  9  /
PL/SQL procedure successfully completed

SQL> with m as (
  2  select t.*, row_number() over(partition by a order by d) rn  from test t
  3  )
  4  select a, b, d from m where rn = 1;
    A     B     D
----- ----- -----
   12    12    22
   13    13    33
   14    14 
SQL> drop table test purge;
Table dropped

SQL> 
nayi_224 2018-10-11
  • 打赏
  • 举报
回复
with tab1 as (
select 1 a, 1 b, 9 c from dual union all
select 1 a, 1 b, 8 c from dual union all
select 1 a, 2 b, null c from dual union all
select 1 a, 2 b, 9 c from dual union all
select 1 a, 3 b, null c from dual union all
select 1 a, 4 b, null c from dual 
)
,tab2 as (
select t1.a,
       t1.b,
       t1.c,
       count(1) over(partition by t1.a, t1.b) cot,
       count(t1.c) over(partition by t1.a, t1.b) not_null
  from tab1 t1
)
select t1.a, t1.b, t1.c from tab2 t1 where t1.cot = t1.not_null union all
select t1.a, t1.b, max(t1.c) from tab2 t1 where t1.cot != t1.not_null group by t1.a, t1.b
;

三火一木燊 2018-10-11
  • 打赏
  • 举报
回复
引用 3 楼 wmxcn2000 的回复:
楼主给出测试数据,和你想要的结果。


sql查出的几种结果举例

字段a 字段b discrepancy
12 12 22
12 12 null

13 13 null
13 13 33

14 14 null
14 14 null

15 15 55
15 15 56

想得到的结果
12 12 22
13 13 33
14 14 null
15 15 55
15 15 56
三火一木燊 2018-10-11
  • 打赏
  • 举报
回复
字段a 字段b discrepancy
12 12 22
12 12 null

13 13 null
13 13 33

14 14 null
14 14 null

预想结果
12 12 22
13 13 33
14 14 null
卖水果的net 2018-10-11
  • 打赏
  • 举报
回复
楼主给出测试数据,和你想要的结果。
三火一木燊 2018-10-11
  • 打赏
  • 举报
回复
引用 1 楼 nayi_224 的回复:
select*from (
select
discrepancy01 as discrepancy
from po_data

union all

select
discrepancy02 as discrepancy
from po_data)
where discrepancy is not null


引用 1 楼 nayi_224 的回复:
select*from (
select
discrepancy01 as discrepancy
from po_data

union all

select
discrepancy02 as discrepancy
from po_data)
where discrepancy is not null

sql没写清楚,为空是随机出现的,有可能是第一行也有可能是第二行,还有可能是两个都是空的,这样的情况下怎么取出非空的值或者两个都为空只取一条
nayi_224 2018-10-11
  • 打赏
  • 举报
回复
select*from ( select discrepancy01 as discrepancy from po_data union all select discrepancy02 as discrepancy from po_data) where discrepancy is not null

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧