--查询重复的记录
SELECT a.* FROM Contact a,(
SELECT Cust_ID,Contact_Name
FROM Contact
GROUP BY Cust_ID,Contact_Name
HAVING COUNT(1)>1
) AS b
WHERE a.Cust_ID=b.Cust_ID AND a.Contact_Name=b.Contact_Name
---查询重复最小ID的记录
select MIN(aa.Contact_ID) id,Contact_Name from (
SELECT a.* FROM Contact a,(
SELECT Cust_ID,Contact_Name
FROM Contact
GROUP BY Cust_ID,Contact_Name
HAVING COUNT(1)>1
) AS b
WHERE a.Cust_ID=b.Cust_ID AND a.Contact_Name=b.Contact_Name
) aa
group by aa.Contact_Name
having COUNT(1)>1
--删除重复的记录
delete cc from Contact cc join(
select MIN(aa.Contact_ID) id,Contact_Name from (
SELECT a.* FROM Contact a,(
SELECT Cust_ID,Contact_Name
FROM Contact
GROUP BY Cust_ID,Contact_Name
HAVING COUNT(1)>1
) AS b
WHERE a.Cust_ID=b.Cust_ID AND a.Contact_Name=b.Contact_Name
) aa
group by aa.Contact_Name
having COUNT(1)>1
) tmp on cc.Contact_ID <>tmp.id and cc.Contact_Name =tmp.Contact_Name
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。