create table tb(字段1 varchar(50),字段2 varchar(10)) Insert into tb select 'LCG00000JHW42ZA3JRE1','LCG0-8-4' union all select 'LCG00000JHW42ZA3JRE1','LCG0-5-J' union all select 'LCG00000JHW42ZA3JRE1','LCG0-8-4' union all select 'LCG00000JHW42ZA3JRE1','LCG0-6-H' union all select 'LCG00000JHW42ZA3JRE1','LCG0-2H' union all select 'LCG00000JHW42ZA3JRE1','LCG0-5-J'select a.* from tb a,(select * from tb group by 字段1,字段2 having count(*)>1)b where a.字段1=b.字段1 and a.字段2=b.字段2 order by 2 desc--結果 字段1 字段2 ----------------------------------------------- LCG00000JHW42ZA3JRE1 LCG0-8-4 LCG00000JHW42ZA3JRE1 LCG0-8-4 LCG00000JHW42ZA3JRE1 LCG0-5-J LCG00000JHW42ZA3JRE1 LCG0-5-J
select * from 表A a where exists(select count(*) from 表A where 字段1=a.字段1 and 字段2=a.字段2 group by 字段1,字段2 having count(*)>1) order by 字段2 descLCG00000JHW42ZA3JRE1 LCG0-8-4 LCG00000JHW42ZA3JRE1 LCG0-8-4 LCG00000JHW42ZA3JRE1 LCG0-5-J LCG00000JHW42ZA3JRE1 LCG0-5-J
Insert into tb
select 'LCG00000JHW42ZA3JRE1','LCG0-8-4'
union all select 'LCG00000JHW42ZA3JRE1','LCG0-5-J'
union all select 'LCG00000JHW42ZA3JRE1','LCG0-8-4'
union all select 'LCG00000JHW42ZA3JRE1','LCG0-6-H'
union all select 'LCG00000JHW42ZA3JRE1','LCG0-2H'
union all select 'LCG00000JHW42ZA3JRE1','LCG0-5-J'select a.* from tb a,(select * from tb group by 字段1,字段2 having count(*)>1)b
where a.字段1=b.字段1 and a.字段2=b.字段2 order by 2 desc--結果
字段1 字段2
-----------------------------------------------
LCG00000JHW42ZA3JRE1 LCG0-8-4
LCG00000JHW42ZA3JRE1 LCG0-8-4
LCG00000JHW42ZA3JRE1 LCG0-5-J
LCG00000JHW42ZA3JRE1 LCG0-5-J
where exists(select count(*) from 表A where 字段1=a.字段1 and 字段2=a.字段2 group by 字段1,字段2 having count(*)>1)
order by 字段2 descLCG00000JHW42ZA3JRE1 LCG0-8-4
LCG00000JHW42ZA3JRE1 LCG0-8-4
LCG00000JHW42ZA3JRE1 LCG0-5-J
LCG00000JHW42ZA3JRE1 LCG0-5-J