62,025
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(
id INT IDENTITY(1,1) PRIMARY KEY,
c1 VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO a VALUES ('b');
INSERT INTO a VALUES ('b');
INSERT INTO a VALUES ('c');
INSERT INTO a VALUES ('d');
INSERT INTO a VALUES ('d');
-- 以上为测试表及测试数据
--查看删除前的数据
SELECT * FROM a
/*
id c1
1 b
2 b
3 c
4 d
5 d
*/
--以 c1 分组,保留同组中id最小的那一条,其它重复数据删除
DELETE t
FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY c1 ORDER BY id) AS rid,* FROM a
) AS t
WHERE t.rid>1
--查看删除后的数据
SELECT * FROM a
/*
id c1
1 b
3 c
4 d
*/
DELETE a FROM a
JOIN (
SELECT MIN(Id) Id,Name FROM dbo.a
GROUP BY Name
HAVING COUNT(1) > 1
) tmp ON a.Name = tmp.Name AND a.Id <> tmp.Id