SQL SERVER删除重复行

构造临时表：

CREATE TABLE #Test (
ID INT IDENTITY(1,1),
Name VARCHAR(16),
RegisterDate Date,
Hometown VARCHAR(256)
)

INSERT INTO #Test VALUES('wangerxiao','2012-03-10','Sichuan'),
('wangerxiao','2012-03-10','Sichuan'),
('zhangxiaobo','2012-03-17','Hebei'),
('zhangxiaobo','2012-03-17','Hebei'),
('zhangxiaobo','2012-03-17','Hebei'),
('liuli','2012-01-17','Jiangsu'),
('laiying','2011-03-17','Hubei')

其中该数据包含非重复的数字ID.

 1.每个重复数据都可以看做一组数据(ID)不同,我们按照待比对的数据列分组,只保留其中最大(最小)ID的数据行

DELETE FROM #Test WHERE ID NOT IN (SELECT MAX(ID) FROM #Test 
GROUP BY Name,RegisterDate,Hometown)

 

2.通过临时表中转

SELECT MAX(ID) AS MaxID,Name,RegisterDate,Hometown INTO #Test2 FROM #Test GROUP BY Name,
RegisterDate,Hometown

DELETE FROM #Test

--IDENTITY_INSERT

SET IDENTITY_INSERT #Test ON

INSERT INTO #Test(ID,Name,RegisterDate,Hometown) SELECT * FROM #Test2

没有ID的情况

CREATE TABLE #Test (
Name VARCHAR(16),
RegisterDate Date,
Hometown VARCHAR(256)
)

INSERT INTO #Test VALUES('wangerxiao','2012-03-10','Sichuan'),
('wangerxiao','2012-03-10','Sichuan'),
('zhangxiaobo','2012-03-17','Hebei'),
('zhangxiaobo','2012-03-17','Hebei'),
('zhangxiaobo','2012-03-17','Hebei'),
('liuli','2012-01-17','Jiangsu'),
('laiying','2011-03-17','Hubei')

 

1.构造ROWID标识,按照待比对的数据分组,删除那些ROWID大于1的

DELETE FROM T2 FROM  (SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Name,RegisterDate,Hometown ORDER BY Name,
RegisterDate,Hometown) AS ROWID,Name,RegisterDate,Hometown FROM #Test) AS T) AS T2
WHERE T2.ROWID > 1

 

2.通过临时表中转

SELECT DISTINCT * INTO #Test3 FROM #Test
DELETE FROM #Test
INSERT INTO #Test SELECT * FROM #Test3

暂时想到这么多,如果还有其他的好方法,欢迎大家指点,谢谢