比如说: A B C 1 aa 123 ------与下面第6行的值一样 2 a 134 3 b 12 4 aa 15 5 aa 99 6 aa 123 7 c 77 最后我要拿到的结果的是B字段下面的那个aa值 和C字段下面的123值
SELECT B FROM ( select B, count(B) over (partition by B) bnum, count(C) over (partition by B,C) cnum from temp) WHERE bnum>1 AND cnum>1;
with temp as( select 1 A,'aa' B,123 C from dual union all select 2 A,'a' B,124 C from dual union all select 3 A,'b' B,12 C from dual union all select 4 A,'aa' B,15 C from dual union all select 5 A,'aa' B,99 C from dual union all select 6 A,'aa' B,123 C from dual union all select 7 A,'c' B,77 C from dual )select B,C from temp having count(*) > 1 group by B,C 这个不知道满足不?
with tmp as( select '1' A, 'aa' B, '123' C from dual union all select '2' A, 'a' B, '134' C from dual union all select '3' A, 'b' B, '12' C from dual union all select '4' A, 'aa' B, '15' C from dual union all select '5' A, 'aa' B, '99' C from dual union all select '6' A, 'aa' B, '123' C from dual union all select '7' A, 'c' B, '77' C from dual ) select a.b,a.c from (select * from tmp) a,tmp b where a.B=b.B and a.C=b.C and a.a<>b.a and rownum =1;
create table test21 ( A int , B varchar(20), C int )insert all into test21 values(1,'aa',123) into test21 values(2,'a',134) into test21 values(3,'b',12) into test21 values(4,'aa',15) into test21 values(5,'aa',99) into test21 values(6,'aa',123) into test21 values(7,'c',77) select * from dualselect B,C from test21 group by B,C having count(*)>1 我自己也复习下,呵呵
三楼的办法可行,我也说个,呵呵,不知道是不是你要的意思,我的办法很简单,就是做一个简单的聚合,因为我认为你就是要查找B、C都重复的B字段,所以我想按照B、C做一个GROUP BY就行了,不过不知道是不是你说的意思: SELECT B FROM( SELECT B,C,COUNT(A) FROM <table_name> GROUP BY B,C HAVING COUNT(A)>1)
A B C
1 aa 123 ------与下面第6行的值一样
2 a 134
3 b 12
4 aa 15
5 aa 99
6 aa 123
7 c 77
最后我要拿到的结果的是B字段下面的那个aa值 和C字段下面的123值
select B,
count(B) over (partition by B) bnum,
count(C) over (partition by B,C) cnum
from temp)
WHERE bnum>1 AND cnum>1;
select 1 A,'aa' B,123 C from dual
union all
select 2 A,'a' B,124 C from dual
union all
select 3 A,'b' B,12 C from dual
union all
select 4 A,'aa' B,15 C from dual
union all
select 5 A,'aa' B,99 C from dual
union all
select 6 A,'aa' B,123 C from dual
union all
select 7 A,'c' B,77 C from dual
)select B,C from temp having count(*) > 1 group by B,C
这个不知道满足不?
select '1' A, 'aa' B, '123' C from dual union all
select '2' A, 'a' B, '134' C from dual union all
select '3' A, 'b' B, '12' C from dual union all
select '4' A, 'aa' B, '15' C from dual union all
select '5' A, 'aa' B, '99' C from dual union all
select '6' A, 'aa' B, '123' C from dual union all
select '7' A, 'c' B, '77' C from dual
)
select a.b,a.c from (select * from tmp) a,tmp b where a.B=b.B and a.C=b.C and a.a<>b.a and rownum =1;
create table test21
(
A int ,
B varchar(20),
C int
)insert all
into test21 values(1,'aa',123)
into test21 values(2,'a',134)
into test21 values(3,'b',12)
into test21 values(4,'aa',15)
into test21 values(5,'aa',99)
into test21 values(6,'aa',123)
into test21 values(7,'c',77)
select * from dualselect B,C from test21 group by B,C having count(*)>1
我自己也复习下,呵呵
SELECT B FROM(
SELECT B,C,COUNT(A)
FROM <table_name>
GROUP BY B,C
HAVING COUNT(A)>1)