刪除Table表中的重復(fù)行的方法
更新時(shí)間：2010年06月08日 00:22:16 作者：
在寫SQL的時(shí)候經(jīng)常會(huì)有這樣的需求: 在一個(gè)Table中會(huì)有多條重復(fù)的數(shù)據(jù),如何有效的取出來不重復(fù)的數(shù)據(jù),或者是刪除掉重復(fù)的數(shù)據(jù),或者取出某列重復(fù)值的第一條數(shù)據(jù).
利用SQL Server 2005的新功能NOW_NUMBER和CTE可以很好的實(shí)現(xiàn).
舉例說明如下:
建立測(cè)試數(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)生多余的列.
首先會(huì)分配一個(gè)列號(hào)碼,以Col1,Col2組合來分區(qū)排序.
SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1
得到的序號(hào)如下:
很明顯的是重復(fù)列都分組分割排序,只需要delete掉排序序號(hào)>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)建一個(gè)標(biāo)識(shí)鍵唯一的表記一列.
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,這種方法借助一個(gè)新的table,把不重復(fù)的結(jié)果集轉(zhuǎn)移到新table中.
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建議采用第一種和第三種方法,第一種多見于T-SQL的編程中,第三種在ETL中常常使用.
舉例說明如下:
建立測(cè)試數(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)生多余的列.
首先會(huì)分配一個(gè)列號(hào)碼,以Col1,Col2組合來分區(qū)排序.
復(fù)制代碼 代碼如下:
SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1
得到的序號(hào)如下:
很明顯的是重復(fù)列都分組分割排序,只需要delete掉排序序號(hào)>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)建一個(gè)標(biāo)識(shí)鍵唯一的表記一列.
復(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,這種方法借助一個(gè)新的table,把不重復(fù)的結(jié)果集轉(zhuǎn)移到新table中.
復(fù)制代碼 代碼如下:
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建議采用第一種和第三種方法,第一種多見于T-SQL的編程中,第三種在ETL中常常使用.
相關(guān)文章
sqlserver清除完全重復(fù)的數(shù)據(jù)只保留重復(fù)數(shù)據(jù)中的第一條
根據(jù)autoID刪除臨時(shí)表#tmp中的重復(fù)數(shù)據(jù)，只保留每組重復(fù)數(shù)據(jù)中的第一條2014-07-07
MDF文件在SQL Server中的恢復(fù)技術(shù)
MDF文件在SQL Server中的恢復(fù)技術(shù)...2007-01-01
SQLSERVER Pager store procedure分頁存儲(chǔ)過程
SQL SERVER（2005)以上版本可用,相對(duì)應(yīng)的頁面邏輯中寫的對(duì)應(yīng)調(diào)用該存儲(chǔ)過程的方法2010-06-06
觸發(fā)器是一種特殊的存儲(chǔ)過程，觸發(fā)器是通過事件觸發(fā)可以自動(dòng)調(diào)用執(zhí)行的。在sql2005中，觸發(fā)器可以分為dml觸發(fā)器和ddl觸發(fā)器。下面通過本篇文章給大家深入淺析sqlserver觸發(fā)器，需要的朋友可以參考下2015-10-10
這篇文章主要為大家詳細(xì)介紹了SQL Server常見問題及解決方法，包括SQL Server連接問題，SQL Server日志問題，SQL Server查詢很久等問題，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
網(wǎng)上關(guān)于SQL優(yōu)化的教程很多,但是比較雜亂。今天我們來從一個(gè)簡(jiǎn)單的例子來探討下SQLServer地址搜索性能優(yōu)化2017-01-01
今天寫MySQL時(shí)遇到使用表別名的問題，這里重新總結(jié)一下,需要的朋友可以參考下2017-02-02
SQL SERVER調(diào)用存儲(chǔ)過程小結(jié)
在SQL Server數(shù)據(jù)庫的維護(hù)或者Web開發(fā)中，有時(shí)需要在存儲(chǔ)過程或者作業(yè)等其他數(shù)據(jù)庫操作中調(diào)用其它的存儲(chǔ)過程，下面介紹其調(diào)用的方法2015-11-11
存儲(chǔ)過程解密(破解函數(shù),過程,觸發(fā)器,視圖.僅限于SQLSERVER2000)
解密指定存儲(chǔ)過程 exec sp_decrypt '存儲(chǔ)過程名'2009-05-05
SQLSERVER收集語句運(yùn)行的統(tǒng)計(jì)信息并進(jìn)行分析
例如語句的編譯時(shí)間、執(zhí)行時(shí)間、做了多少次磁盤讀等,如果DBA能夠把問題語句單獨(dú)測(cè)試運(yùn)行，可以在運(yùn)行前打開下面這三個(gè)開關(guān)，收集語句運(yùn)行的統(tǒng)計(jì)信息。這些信息對(duì)分析問題很有價(jià)值,感興趣的朋友可以了解下2013-01-01