c1 c2 c3
--------------------
1 a cd
1 c we
1 c ilj
2 d hn
2 d ni
3 e oi
3 f no
要求:以字段c1为分组,每组中若c2的所有值都相同,不显示;c2存在不同的值,显示整条记录。
比如这里查出来的结果应该是:
c1 c2 c3
--------------------
1 a cd
1 c we
1 c ilj
3 e oi
3 f no
向各位请教要实现此功能应该怎么写SQL语句呢?
SQL> select c1, c2, c3
2 from (select tt.*,
3 count(*) over(partition by c1, c2) ct1,
4 count(*) over(partition by c1) ct2
5 from tt)
6 where ct1 <> ct2
7 ;
C1 C2 C3
---------- -- ----------
1 a cd
1 c we
1 c ilj
3 e oi
3 f no
2 c2,
3 c3
4 from
5 (select c1,
6 c2,
7 c3,
8 count(distinct c2) over(partition by c1) cnt
9 from tt)
10 where cnt <> 1;
C1 C2 C3
---------- -- --------
1 a cd
1 c we
1 c ilj
3 e oi
3 f no