sql刪除重復(fù)數(shù)據(jù)的詳細(xì)方法
發(fā)布日期：2022-01-02 16:06 | 文章來(lái)源：腳本之家
一. 刪除完全重復(fù)的記錄
完全重復(fù)的數(shù)據(jù)，通常是由于沒(méi)有設(shè)置主鍵/唯一鍵約束導(dǎo)致的。
測(cè)試數(shù)據(jù)：
復(fù)制代碼 代碼如下:
if OBJECT_ID('duplicate_all') is not null
drop table duplicate_all
GO
create table duplicate_all
(
c1 int,
c2 int,
c3 varchar(100)
)
GO
insert into duplicate_all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 2,200,'bbb' union all
select 3,300,'ccc' union all
select 4,400,'ddd' union all
select 5,500,'eee'
GO
(1) 借助臨時(shí)表
利用DISTINCT得到單條記錄，刪除源數(shù)據(jù)，然后導(dǎo)回不重復(fù)記錄。
如果表不大的話，可以把所有記錄導(dǎo)出一次，然后truncate表后再導(dǎo)回，這樣可以避免delete的日志操作。
復(fù)制代碼 代碼如下:
if OBJECT_ID('tempdb..#tmp') is not null
drop table #tmp
GO
select distinct * into #tmp
from duplicate_all
where c1 = 1
GO
delete duplicate_all where c1 = 1
GO
insert into duplicate_all
select * from #tmp
(2) 使用ROW_NUMBER
復(fù)制代碼 代碼如下:
with tmp
as
(
select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY(getdate())) as num
from duplicate_all
where c1 = 1
)
delete tmp where num > 1
如果多個(gè)表有完全重復(fù)的行，可以考慮通過(guò)UNION將多個(gè)表聯(lián)合，插到一個(gè)新的同結(jié)構(gòu)的表，SQL Server會(huì)幫助去掉表和表之間的重復(fù)行。
二. 刪除部分重復(fù)的記錄
部分列重復(fù)的數(shù)據(jù)，通常表上是有主鍵的，可能是程序邏輯造成了多行數(shù)據(jù)列值的重復(fù)。
測(cè)試數(shù)據(jù)：
復(fù)制代碼 代碼如下:
if OBJECT_ID('duplicate_col') is not null
drop table duplicate_col
GO
create table duplicate_col
(
c1 int primary key,
c2 int,
c3 varchar(100)
)
GO
insert into duplicate_col
select 1,100,'aaa' union all
select 2,100,'aaa' union all
select 3,100,'aaa' union all
select 4,100,'aaa' union all
select 5,500,'eee'
GO
(1) 唯一索引
唯一索引有個(gè)忽略重復(fù)建的選項(xiàng)，在創(chuàng)建主鍵約束/唯一鍵約束時(shí)都可以使用這個(gè)索引選項(xiàng)。
復(fù)制代碼 代碼如下:
if OBJECT_ID('tmp') is not null
drop table tmp
GO
create table tmp
(
c1 int,
c2 int,
c3 varchar(100),
constraint UQ_01 unique(c2,c3) with(IGNORE_DUP_KEY = ON)
)
GO
insert into tmp
select * from duplicate_col
select * from tmp
(2) 借助主鍵/唯一鍵來(lái)刪除
通常會(huì)選擇主鍵/唯一鍵的最大/最小值保留，其他行刪除。以下只保留重復(fù)記錄中c1最小的行。
復(fù)制代碼 代碼如下:
delete from duplicate_col
where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3))
--或者
復(fù)制代碼 代碼如下:
delete from duplicate_col
where c1 not in (select min(c1) from duplicate_col group by c2,c3)
如果要保留重復(fù)記錄中的第N行，可以參考05.取分組中的某幾行。
(3) ROW_NUMBER
和刪除完全重復(fù)記錄的寫(xiě)法基本一樣。
復(fù)制代碼 代碼如下:
with tmp
as
(
select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY(getdate())) as num
from duplicate_col
)
delete tmp where num > 1
select * from duplicate_col
用SQL語(yǔ)句,刪除掉重復(fù)項(xiàng)只保留一條
在幾千條記錄里,存在著些相同的記錄,如何能用SQL語(yǔ)句,刪除掉重復(fù)的呢
1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷，只留有rowid最小的記錄
delete from people
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)
3、查找表中多余的重復(fù)記錄（多個(gè)字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
6.消除一個(gè)字段的左邊的第一位：
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消除一個(gè)字段的右邊的第一位：
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假刪除表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信