有表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字段重复的返回
解决方案 »
- Oralce表建了怎么不能用
- 一简单问题。。 关于存储过程, 函数。。。返回值
- 请教索引覆盖?
- 你一定能够解决(orcale 建模)
- 求助:查询语句的问题,帮忙看看怎么实现
- 高分求SQL优化问题
- 请问:引用远程数据链路中,关于使用case when 语句的问题,谢谢
- 排序的超级难的问题~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 如何在Oracle的表的任意位置中新插入一个或多个字段,或者调整表中字段的顺序?
- sqlldr导入txt数据,在数据库成生一个log_record表(最好是存储过程) 急急急..!
- 访问文件路径的问题 XMLPARSER.parse(prsNew, vDir)
- 代码错误
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