查询重复数据

 

1、使用GROUP BY...HAVING查询重复数据

--找出字段1重复的行
SELECT  * FROM  表名 WHERE  字段1 IN (SELECT  字段1  FROM  表名  GROUP BY 字段1 
HAVING  COUNT(*) > 1)
--找出字段1,字段2均重复的行
SELECT  a.* FROM 表名 AS a LEFT OUTER JOIN
 (SELECT 字段1,字段2 FROM 表名 GROUP BY 字段1,字段2
HAVING (COUNT(*) > 1)) AS b ON a.字段1= b.字段1
WHERE (a.字段2 = b.字段2)

2、根据字段id(唯一)删除重复的行，只保留最大id或最小id行，下面示例只保留id最小行 

DELETE FROM 表名 WHERE 

(id IN (SELECT a.id FROM 表名 a LEFT OUTER JOIN (SELECT 字段1, 字段2 FROM 表名  GROUP BY 字段1, 字段2 HAVING (COUNT(*) > 1)) AS b ON a.字段1= b.字段1 WHERE (a.字段2= b.字段2))) 
AND 
(id NOT IN(SELECT min(id) FROM 表名 GROUP BY 字段1, 字段2 HAVING (COUNT(*) > 1)))

 

3.select * from #T a where ID=(select min(ID) from #T where Name=a.Name)