SQL SERVER 查询、删除重复数据

查询删除重复数据，只保留一条记录：

1.根据单字段，查询表中该字段重复出现记录：

SELECT * FROM Table

WHERE Parameter IN

(SELECT Parameter FROM Table

GROUP BY Parameter HAVING COUNT(Parameter ) > 1)

分析：

SELECT Parameter FROM Table

GROUP BY Parameter HAVING COUNT(Parameter ) > 1

根据字段分组，查询出表中出现重复（字段数量>1）的字段集

-----------------------------华丽的分割线---------------------------------

2.根据单字段，删除表中该字段重复出现记录，保留最小主键ID

DELETE FROM Table

WHERE Parameter IN

(SELECT Parameter FROM Table

GROUP BY Parameter HAVING COUNT(Parameter) > 1)

AND ID NOT IN

(SELECT MIN(ID) FROM Table

GROUP BY Parameter HAVING COUNT(Parameter)>1)

-----------------------------华丽的分割线---------------------------------

3.根据多个字段查询表中重复的记录

SELECT * FROM Table a

WHERE EXISTS

(SELECT Para1 ,Para2 FROM Table b

WHERE a.Para1 = b.Para1 AND

a.Para1 = b.Para2 GROUP BY Para1 ,Para2

HAVING COUNT(*) > 1 )

-----------------------------华丽的分割线---------------------------------

4. 根据多个字段 删除表中重复的记录，保留最小主键记录

DELETE A

FROM Table A ,

(SELECT Para1,Para2 FROM Table

GROUP BY Para1,Para2

HAVING COUNT(*) > 1 ) B

WHERE A.Para1=B.Para1 AND a.Para2= b.Para2

AND A.ID NOT IN (SELECT MIN(ID) AS ID

FROM Table

GROUP BY Para1,Para2

HAVING COUNT(*) > 1)

-----------------------------华丽的分割线---------------------------------

5.查询多个字段重复记录，不包含最小主键ID

SELECT * FROM Table a

WHERE EXISTS

(SELECT Para1,Para2 FROM Table b

WHERE a.Para1 = b.Para1 AND a. Para2 = b.Para2

GROUP BY Para1,Para2

HAVING COUNT(*) > 1)

AND ID NOT IN

(SELECT MIN(ID) FROM Table

GROUP BY Para1,Para2

HAVING COUNT(*)>1)

-----------------------------华丽的分割线---------------------------------

参考： http://blog.csdn.net/anya/article/details/6407280/