关于重复记录

　　日常工作中经常会遇到重复的数据，今天写下重复数据的查询和删除。备忘贴。

　　先来看下表结构吧

    

   

查询重复记录
方式A
SELECT * FROM MyDuplicateData a  WHERE id<(SELECT MAX(id) FROM MyDuplicateData WHERE stuname=a.stuname)

方式B
SELECT * FROM MyDuplicateData a WHERE EXISTS(SELECT 1 FROM MyDuplicateData b WHERE a.stuid=b.stuid AND a.stuname=b.stuname AND a.birthdat=b.birthdat AND
a.    areaorganid=b.areaorganid AND a.id<b.id )

删除
方式A
DELETE a FROM MyDuplicateData a WHERE EXISTS(SELECT 1 FROM MyDuplicateData b WHERE a.stuid=b.stuid AND a.stuname=b.stuname AND a.birthdat=b.birthdat AND
a.areaorganid=b.areaorganid AND a.id<b.id )

方式B（使用CTE）
;WITH mycte AS 
(
SELECT ROW_NUMBER() OVER (PARTITION BY stuname ORDER BY id ASC) AS num,* FROM MyDuplicateData
)
DELETE FROM mycte WHERE id NOT IN(SELECT id FROM mycte WHERE num=1)