DB2數據庫重復記錄刪除sql語句

重復記錄刪除方法在DB2中有很多種，下面我來給大家介紹幾種比較實用並且性能也不錯的刪除重復記錄的sql語句吧。

--1.查詢重復數據

 代碼如下 復制代碼

select * from tableA where catentry_id in (select distinct catentry_id from tableA group by catentry_id,descitem_id having count(*)>1)@

--2.建立臨時表，將所有重復數據存入該表

 代碼如下 復制代碼

create table detailtemp1 like tableA@

insert into detailtemp1
    select * from tableA
    where catentry_id in (select distinct catentry_id from tableA group by catentry_id,descitem_id having count(*)>1)@
  
select * from detailtemp1@

--3。從原表中刪除所有出現重復情況的記錄

 代碼如下 復制代碼

delete from tableA where catentry_id in (select distinct catentry_id from tableA group by catentry_id,descitem_id having count(*)>1)@

--4。用group by 分組，將臨時表中的不重復記錄插入原表中

 代碼如下 復制代碼

insert into tableA
select catentry_id,descitem_id,max(content)
from detailtemp1
group by catentry_id,descitem_id@

select * from tableA where catentry_id in (select distinct catentry_id from tableA group by catentry_id,descitem_id having count(*)>1)@

 
--5。刪除臨時表

 代碼如下 復制代碼

drop table detailtemp1@

--end

另外再分享幾種辦法

　2、DB2刪除重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄

 代碼如下 復制代碼 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

　　3、查找表中多余的重復記錄（多個字段）

 代碼如下 復制代碼

　　select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

　　4、DB2刪除重復記錄（多個字段），只留有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、查找表中多余的重復記錄（多個字段），不包含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. 處理bas_information中去重，根據

 代碼如下 復制代碼 t.PISField001,t.PISField011,t.areaCode
export to e:/bas_information.txt of del select s.HOUSEHOLDS, s.PISFIELD000, s.PISFIELD001, s.PISFIELD002,
s.PISFIELD003, s.PISFIELD004, s.PISFIELD005, s.PISFIELD006, s.PISFIELD011,
s.PISFIELD012, s.PISFIELD013, s.PISFIELD014, s.PISFIELD015, s.PISFIELD016,
s.DEADDATE, s.QIANRUDATE, s.ZHIYE, s.JIATING, s.AREACODE, s.REG_DATE,
s.ISLOGOUT from (select t.*,rownumber() over(partition by t.PISField001,t.PISField011,t.areaCode) as rn from bas_information t ) s where s.rn = 1;