刪除Table表中的重復(fù)行的方法
更新時間：2010年06月08日 00:22:16 作者：
在寫SQL的時候經(jīng)常會有這樣的需求: 在一個Table中會有多條重復(fù)的數(shù)據(jù),如何有效的取出來不重復(fù)的數(shù)據(jù),或者是刪除掉重復(fù)的數(shù)據(jù),或者取出某列重復(fù)值的第一條數(shù)據(jù).
利用SQL Server 2005的新功能NOW_NUMBER和CTE可以很好的實現(xiàn).
舉例說明如下:
建立測試數(shù)據(jù):
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
可以查看到重復(fù)的數(shù)據(jù)有:
SELECT Col1, Col2, COUNT(*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT(*) > 1
接下來介紹如何delete掉重復(fù)的數(shù)據(jù):
1.NOW_NUMBER:SQL Server 2005添加了很好用的RANKING函數(shù)(NOW_NUMBER,RANK,DENSE_RANK,NTILE),利用NOW_NUMBER()OVER(PARTITION GY)最為直接,也最為方便,不能修改表或者產(chǎn)生多余的列.
首先會分配一個列號碼,以Col1,Col2組合來分區(qū)排序.
SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1
得到的序號如下:
很明顯的是重復(fù)列都分組分割排序,只需要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.創(chuàng)建一個標(biāo)識鍵唯一的表記一列.
ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;
刪除找出與Col1,Col2相同并且比Dup1.PK大的記錄,也就是保留重復(fù)值中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,把不重復(fù)的結(jié)果集轉(zhuǎn)移到新table中.
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建議采用第一種和第三種方法,第一種多見于T-SQL的編程中,第三種在ETL中常常使用.
舉例說明如下:
建立測試數(shù)據(jù):
復(fù)制代碼 代碼如下:
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
可以查看到重復(fù)的數(shù)據(jù)有:
復(fù)制代碼 代碼如下:
SELECT Col1, Col2, COUNT(*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT(*) > 1
接下來介紹如何delete掉重復(fù)的數(shù)據(jù):
1.NOW_NUMBER:SQL Server 2005添加了很好用的RANKING函數(shù)(NOW_NUMBER,RANK,DENSE_RANK,NTILE),利用NOW_NUMBER()OVER(PARTITION GY)最為直接,也最為方便,不能修改表或者產(chǎn)生多余的列.
首先會分配一個列號碼,以Col1,Col2組合來分區(qū)排序.
復(fù)制代碼 代碼如下:
SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1
得到的序號如下:
很明顯的是重復(fù)列都分組分割排序,只需要delete掉排序序號>1的即可.
復(fù)制代碼 代碼如下:
--用到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.創(chuàng)建一個標(biāo)識鍵唯一的表記一列.
復(fù)制代碼 代碼如下:
ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;
刪除找出與Col1,Col2相同并且比Dup1.PK大的記錄,也就是保留重復(fù)值中PK最小的記錄.
復(fù)制代碼 代碼如下:
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,把不重復(fù)的結(jié)果集轉(zhuǎn)移到新table中.
復(fù)制代碼 代碼如下:
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建議采用第一種和第三種方法,第一種多見于T-SQL的編程中,第三種在ETL中常常使用.
相關(guān)文章
sql時間段切分實現(xiàn)每隔x分鐘出一份高速門架車流量
本文主要介紹了sql時間段切分實現(xiàn)每隔x分鐘出一份高速門架車流量，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
本文主要介紹了SQL Server里書簽查找的性能傷害，具有很好的參考價值。下面跟著小編一起來看下吧2017-03-03
這篇文章主要介紹了分區(qū)表場景下的 SQL 優(yōu)化，幫助大家更好的理解和學(xué)習(xí)SQL，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了sqlserver:查詢鎖住sql以及解鎖方法，具有一定參考價值，這里分享給大家，供需要的朋友參考。2017-10-10
SQL?Server?實例之間傳輸?shù)卿浢兔艽a的詳細步驟
本文介紹如何在Windows上運行的SQL?Server的不同實例之間傳輸?shù)卿浢兔艽a，適用于SQL mirroring，SQL AG中的本地logins的遷移或者同步,需要的朋友可以參考下2023-06-06
本篇文章主要給大家介紹開窗函數(shù)，本文有淺入深詳解開窗函數(shù)，有需要的朋友可以參考下2015-08-08
我們在建立數(shù)據(jù)庫的時候，需要為每張表指定一個主鍵，所謂主鍵就是能夠唯一標(biāo)識表中某一行的屬性或?qū)傩越M，一個表只能有一個主鍵，但可以有多個候選索引2011-10-10
Join是關(guān)系型數(shù)據(jù)庫系統(tǒng)的重要操作之一，SQL Server中包含的常用Join：內(nèi)聯(lián)接、外聯(lián)接和交叉聯(lián)接等2012-08-08
本文將介紹三種批量插入數(shù)據(jù)的方法,需要的朋友可以參考下2013-10-10
這篇文章主要介紹了SQL Server 觸發(fā)器，觸發(fā)器是一種特殊類型的存儲過程，它不同于之前的我們介紹的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)被自動調(diào)用執(zhí)行的。而存儲過程可以通過存儲過程的名稱被調(diào)用。需要的朋友可以參考一下2021-09-09