SqlServer: 删除重复数据

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
	DROP TABLE t
GO
CREATE TABLE t(
	id INT,
	[name] NVARCHAR(10)	
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES(1,'a')
INSERT INTO t VALUES(1,'a')
INSERT INTO t VALUES(2,'b')
INSERT INTO t VALUES(2,'b')
INSERT INTO t VALUES(3,'c')
--
SELECT * FROM t
/*
id	name
1	a
1	a
2	b
2	b
3	c
*/
--------- 以上为测试表及测试数据 ------------


;WITH cte AS(
	SELECT row_number() OVER (PARTITION BY id,[name] ORDER BY (SELECT 1)) AS rid,* FROM t	
)
--SELECT * FROM cte WHERE rid=1  --需要保留的数据
DELETE FROM cte WHERE rid>1

SELECT * FROM t
/*
id	name
1	a
2	b
3	c
*/