-- 假设你的表名为 tb ,tb表的第一个字段是 id,第二个字段是 nameselect t1.id, t1.name from tb t1 groupon by t1.id, t1.name having count(t1.id)>=2 order by t1.name, t1.id;
having count(t1.id)>=2 这个是不是应该改成having count(t1.name)>=2
--方法二: select * from tb a where a.rowid > (select min(b.rowid) from tb b where b.name=a.name)
create table tb ( id number, str varchar2(4) ); insert into tb select 1, 'aa' from dual union all select 2, 'bb' from dual union all select 3, 'cc' from dual union all select 4, 'dd' from dual union all select 5, 'bb' from dual union all select 6, 'dd' from dual commit; select * from tb where str in (select str from tb group by str having count(1)>1); drop table tb; /* ID STR ---------- ---- 2 bb 4 dd 5 bb 6 dd */
with t as (select '1' as ID,'aa' as NAME from dual union all select '2' as ID,'bb' as NAME from dual union all select '3' as ID,'cc' as NAME from dual union all select '4' as ID,'dd' as NAME from dual union all select '5' as ID,'bb' as NAME from dual union all select '6' as ID,'dd' as NAME from dual) select * from t where t.NAME in(select t.NAME from t group by t.NAME having count(t.NAME)>1)二楼貌似不可以。。分组的问题
select id ,name from tb where name in ( select name from tb group by name having count(name)>1 );
from tb t1
groupon by t1.id, t1.name
having count(t1.id)>=2
order by t1.name, t1.id;
这个是不是应该改成having count(t1.name)>=2
select * from tb a where a.rowid > (select min(b.rowid) from tb b where b.name=a.name)
(
id number,
str varchar2(4)
);
insert into tb
select 1, 'aa' from dual union all
select 2, 'bb' from dual union all
select 3, 'cc' from dual union all
select 4, 'dd' from dual union all
select 5, 'bb' from dual union all
select 6, 'dd' from dual
commit;
select * from tb where str in (select str from tb group by str having count(1)>1);
drop table tb;
/*
ID STR
---------- ----
2 bb
4 dd
5 bb
6 dd
*/
(select '1' as ID,'aa' as NAME from dual
union all select '2' as ID,'bb' as NAME from dual
union all select '3' as ID,'cc' as NAME from dual
union all select '4' as ID,'dd' as NAME from dual
union all select '5' as ID,'bb' as NAME from dual
union all select '6' as ID,'dd' as NAME from dual)
select * from t where t.NAME in(select t.NAME from t group by t.NAME having count(t.NAME)>1)二楼貌似不可以。。分组的问题
( select name from tb group by name having count(name)>1 );