有表A,在A中有b,c,d,e列,b是关键字,求查询c列全部重复字段的语法
例子
b c d e
1 1 2 0
2 5 5 5
3 2 7 5
4 1 5 0
5 1 7 2
要求返回的记录是:
b c d e
1 1 2 0
4 1 5 0
5 1 7 2
就是c字段重复的返回
例子
b c d e
1 1 2 0
2 5 5 5
3 2 7 5
4 1 5 0
5 1 7 2
要求返回的记录是:
b c d e
1 1 2 0
4 1 5 0
5 1 7 2
就是c字段重复的返回
b c d e
1 1 2 0
2 5 5 5
3 2 7 5
4 1 5 0
5 1 7 2
6 3 0 1
7 2 9 9
要求返回的记录是:
b c d e
1 1 2 0
3 2 7 5
4 1 5 0
5 1 7 2
7 2 9 9
就是c字段有重复的都返回
2 from (
3 select 1 as b,1 as c,2 as d,0 as e from dual
4 union all
5 select 2 as b,5 as c,5 as d,5 as e from dual
6 union all
7 select 3 as b,2 as c,7 as d,5 as e from dual
8 union all
9 select 4 as b,1 as c,5 as d,0 as e from dual
10 union all
11 select 5 as b,1 as c,7 as d,2 as e from dual
12 )zz
13 where zz.c in (select tt.c
14 from (select 1 as b,1 as c,2 as d,0 as e from dual
15 union all
16 select 2 as b,5 as c,5 as d,5 as e from dual
17 union all
18 select 3 as b,2 as c,7 as d,5 as e from dual
19 union all
20 select 4 as b,1 as c,5 as d,0 as e from dual
21 union all
22 select 5 as b,1 as c,7 as d,2 as e from dual
23 )tt
24 group by tt.c
25 having count(*) > 1
26 ); B C D E
---------- ---------- ---------- ----------
5 1 7 2
4 1 5 0
1 1 2 0
select b from
(select b from a group by c having count(c)>1)
where t1.b=b);
- - - -
1 1 2 0
4 1 5 0
5 1 7 2
(select p_code,pid,valueA,valueB,row_number()over(partition by pid order by pid,valueb desc,valuea desc)r from T_TABLE
) where r>1
2 2 1 b 32
3 3 2 c 3
4 4 2 df 45
5 5 3 hghj 7SQL语句如下:
select * from
a_group
where num in
(select num from
(
select num,row_number()over(partition by num order by oth )r from a_group
) where r>1) 结果如下:
1 2 1 b 32
2 1 1 a 23
3 4 2 df 45
4 3 2 c 3
where a.c=b.c and b.cs>1