有一表data里面的資料有部分是重復的，我想把重復的洗掉只保留一條，如何實作？
uj5u.com熱心網友回復：
你紅線框的那一列，資料也不是完全相同啊。舉一個資料完全相同（a,b 兩列重復），如何洗掉去重的方法：
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
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/191319.html
標籤：應用實例