sql删除重复行
1、删除重复行
如何只显示重复数据，或不显示重复数据
显示重复：select id from tablename group by id having count(*)>1
显示未重复：select id from tablename group by id having count(*)=1
--找出重复行
select empno, count(*) from emp e group by empno having(count(*) > 1);
--保留一行 rowid
select min(rowid) from emp group by empno having count(*) > 1;
--找出只有一行数据 不重复
select min(rowid) from emp group by empno having count(*) = 1;
--二者关联
select *
from (select min(rowid) from emp group by empno having count(*) > 1)
union (select min(rowid) from emp group by empno having count(*) = 1)
--删除
--delete from 表名 where 条件
--删除重复
delete from emp
where rowid not in
(select min(rowid) from emp group by empno having count(*) > 1)
and empno not in (select empno from emp group by empno having count(*) = 1);
