17,078
社区成员
发帖
与我相关
我的任务
分享
-- 我就知道,你会有这样的数据 ,简单的修改一下就可以了
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>
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))
;
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>
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
;