SQL语句查找两条以上数据在指定多个字段相同的数据（查找重复记录）； 查找去重（多个字符同时相同）后的记录

查重方法一：

SELECT p1.*
from ss p1,ss p2
where p1.id<> p2.id
and p1.name = p2.name
and p1.idnum=p2.idnum
and p1.employer=p2.employer
and p1.sst=p2.sst
and p1.start2enddate = p2.start2enddate
and p1.sit=p2.sit;

 

SELECT p1.name, p1.idnumber,p1.employer,p1.sst,p1.start2enddate,p1.sit,count(*) as reccount
from ssp1,ssp2
where p1.id<> p2.id
and p1.name = p2.name
and p1.idnumber=p2.idnumber
and p1.employer=p2.employer
and p1.sst= p2.sst
and p1.start2enddate = p2.start2enddate
and p1.sit=p2.sit
group by p1.name, p1.idnumber,p1.employer,p1.sst,p1.start2enddate,p1.sit
having count(*) >=3
order by reccount desc;

以上语句执行顺序：from -> where -> group by -> having -> order by -> select

因此，group by中没有的列，select是选不了的

 

 

查重方法二：

select *
from mytable a
where a.idnumber in
(SELECT idnumber from mytable GROUP BY idnumber HAVING count(*)>1);

 

查去重后数据：

SELECT distinct name,idnum, employer, sst, start2enddate, sit
FROM ss