27,581
社区成员




DECLARE @Count INT
select @Count=COUNT(kcardno) from data group by kcardno
having count(*) > 1
PRINT @Count
WHILE(@Count>1)
BEGIN
DELETE FROM data
WHERE kcardno in
(
select MAX(kcardno) kcardno from data group by kcardno
having count(*) > 1
)
SET @Count=@Count-1
END
可以先用个测试数据表,做下测试。USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
a INT,
b INT,
c INT
)
GO
INSERT INTO t VALUES (1,2,3)
INSERT INTO t VALUES (1,2,3)
INSERT INTO t VALUES (1,2,6)
INSERT INTO t VALUES (2,3,4)
INSERT INTO t VALUES (2,3,5)
INSERT INTO t VALUES (3,1,2)
;WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY c) AS rid,*
FROM t
)
SELECT * FROM cte WHERE rid=1
/*
rid a b c
1 1 2 3
1 2 3 4
1 3 1 2
*/
;WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY c) AS rid,*
FROM t
)
DELETE FROM cte WHERE rid>1
SELECT * FROM t
/*
a b c
1 2 3
2 3 4
3 1 2
*/