刪除Table表中的反復行的辦法。本站提示廣大學習愛好者：（刪除Table表中的反復行的辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是刪除Table表中的反復行的辦法正文
應用SQL Server 2005的新功效NOW_NUMBER和CTE可以很好的完成.
舉例解釋以下:
樹立測試數據:
create table Dup1
(
Col1 int null,
Col2 varchar(20) null
)
insert into Dup1 values
(1, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(3, 'bbb'),
(4, 'ccc'),
(4, 'ddd'),
(5, 'eee')
select * from Dup1
可以檢查到反復的數據有:
SELECT Col1, Col2, COUNT(*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT(*) > 1
接上去引見若何delete失落反復的數據:
1.NOW_NUMBER:SQL Server 2005添加了很好用的RANKING函數(NOW_NUMBER,RANK,DENSE_RANK,NTILE),應用NOW_NUMBER()OVER(PARTITION GY)最為直接,也最為便利,不克不及修正表或許發生過剩的列.
起首會分派一個列號碼,以Col1,Col2組合來分區排序.
SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1
獲得的序號以下:
很顯著的是反復列都分組朋分排序,只須要delete失落排序序號>1的便可.
--用到CTE
WITH DupsD
AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM Dup1
)
DELETE DupsD
WHERE rn > 1;
--或許
DELETE A FROM (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM Dup1) A WHERE A.rn>1
2.創立一個標識鍵獨一的表記一列.
ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;
刪除找出與Col1,Col2雷同而且比Dup1.PK年夜的記載,也就是保存反復值中PK最小的記載.
DELETE Dup1
WHERE EXISTS ( SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK > Dup1.PK );
3.select distant into,這類辦法借助一個新的table,把不反復的成果集轉移到新table中.
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建議采取第一種和第三種辦法,第一種多見於T-SQL的編程中,第三種在ETL中經常應用.
