【数据库-MySql】重复数据处理

#显示重复数据的汇总
SELECT	demo_key,	count(*) AS count FROM	demo_table GROUP BY	demo_key HAVING	count > 1;
#显示重复数据的明细
SELECT * FROM	demo_table WHERE demo_key IN 
(SELECT demo_key FROM demo_table GROUP BY demo_key HAVING count(demo_key) > 1 );


#删除demo_table表中demo_key重复数据 - 但保留rowid最小的那条
delete from demo_table where demo_key in(
 select tmpa.demo_key from 
 (
			select demo_key  from demo_table group by demo_key having count(demo_key) > 1;
  )tmpa
)
and rowid not in 
(
		select tmpb.c  from 
		 (
		select MIN(rowid) as c from demo_table group by demo_key having count(demo_key )>1;
	) tmpb
)
#多字段 - 显示重复数据的汇总
select demo_key1,demo_key2,count(*) from demo_table group by demo_key1,demo_key2 having count(*) > 1  ; 

#多字段 - 显示重复数据的明细
select * from demo_table a  where (a.demo_key1,a.demo_key2) in 
(select demo_key1,demo_key2 from demo_table group by demo_key1,demo_key2 having count(*) > 1)   ;

#多字段 - 删除demo_table表中demo_key1&demo_key2重复数据 - 但保留rowid最小的那条
delete from demo_table   
where (demo_key1,demo_key2) in 
(
	SELECT  tmpa.demo_key1 , tmpa.demo_key2 FROM
	(
	select demo_key1,demo_key2 from demo_table group by demo_key1,demo_key2 having count(*) > 1
	)tmpa
)
and rowid not in 
(
  select tmpb.c  from 
 (
	select min(rowid) as c from demo_table group by demo_key1,demo_key2 having count(*)>1
	)tmpb
)   
#多字段 - 显示重复数据的明细 ,但不显示rowid最小的那条
select * from demo_table   
where (demo_key1,demo_key2) in (select demo_key1,demo_key2 from demo_table group by demo_key1,demo_key2 having count(*) > 1)   
and rowid not in (select min(rowid) from demo_table group by demo_key1,demo_key2 having count(*)>1)