刪除表中的重複記錄

 我們可能會出現這種情況，某個表原來設計不周全，導致表裡面的資料資料重複，那麼，如何對重複的資料進行刪除呢？
    重複的資料可能有這樣兩種情況，第一種時表中只有某些欄位一樣，第二種是兩行記錄完全一樣。
一、對於部分欄位重複資料的刪除
    先來談談如何查詢重複的資料吧。
    下面語句可以查詢出那些資料是重複的：
select 欄位1,欄位2,count(*) from 表名 group by 欄位1,欄位2 having count(*) > 1
    將上面的>號改為=號就可以查詢出沒有重複的資料了。
    想要刪除這些重複的資料，可以使用下面語句進行刪除
delete from 表名 a where 欄位1,欄位2 in
  (select 欄位1,欄位2,count(*) from 表名 group by 欄位1,欄位2 having count(*) > 1)
    上面的語句非常簡單，就是將查詢到的資料刪除掉。不過這種刪除執行的效率非常低，對於大資料量來說，可能會將資料庫弔死。所以我建議先將查詢到的重複的資料插入到一個暫存資料表中，然後對進行刪除，這樣，執行刪除的時候就不用再進行一次查詢了。如下：

CREATE TABLE 暫存資料表 AS
(select 欄位1,欄位2,count(*) from 表名 group by 欄位1,欄位2 having count(*) > 1)
    上面這句話就是建立了暫存資料表，並將查詢到的資料插入其中。
    下面就可以進行這樣的刪除操作了：
delete from 表名 a where 欄位1,欄位2 in (select 欄位1，欄位2 from 暫存資料表);
    這種先建暫存資料表再進行刪除的操作要比直接用一條語句進行刪除要高效得多。

    這個時候，大家可能會跳出來說，什嗎？你叫我們執行這種語句，那不是把所有重複的全都刪除嗎？而我們想保留重複資料中最新的一條記錄啊！大家不要急，下面我就講一下如何進行這種操作。

    在Oracle中，有個隱藏了自動rowid，裡面給每條記錄一個唯一的rowid，我們如果想保留最新的一條記錄，
我們就可以利用這個欄位，保留重複資料中rowid最大的一條記錄就可以了。
    下面是查詢重複資料的一個例子：
select a.rowid,a.* from 表名 a
where a.rowid !=
(
select max(b.rowid) from 表名 b
where a.欄位1 = b.欄位1 and
a.欄位2 = b.欄位2
)
    下面我就來講解一下，上面括弧中的語句是查詢出重複資料中rowid最大的一條記錄。
    而外面就是查詢出除了rowid最大之外的其他重複的資料了。
    由此，我們要重複資料刪除資料，只保留最新的一條資料，就可以這樣寫了：
delete from 表名 a
where a.rowid !=
(
select max(b.rowid) from 表名 b
where a.欄位1 = b.欄位1 and
a.欄位2 = b.欄位2
)

    隨便說一下，上面語句的執行效率是很低的，可以考慮建立暫存資料表，講需要判斷重複的欄位、rowid插入暫存資料表中，然後刪除的時候在進行比較。
create table 暫存資料表 as
  select a.欄位1,a.欄位2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.欄位1,a.欄位2;
delete from 表名 a
where a.rowid !=
(
select b.dataid from 暫存資料表 b
where a.欄位1 = b.欄位1 and
a.欄位2 = b.欄位2
);
commit;

二、對於完全重複記錄的刪除

    對於表中兩行記錄完全一樣的情況，可以用下面語句擷取到去掉重複資料後的記錄：
select distinct * from 表名
可以將查詢的記錄放到暫存資料表中，然後再將原來的表記錄刪除，最後將暫存資料表的資料導回原來的表中。如下：
CREATE TABLE 暫存資料表 AS (select distinct * from 表名);
drop table 正式表;
insert into 正式表 (select * from 暫存資料表);
drop table 暫存資料表;
三、不藉助於中間表來重複資料刪除記錄執行個體
delete from my_table20 where rowid not in (select rowid from my_table20 where rowid in (select max(rowid) from my_table20 group by deptno)) and deptno in (select distinct(deptno) from my_table20)

 

1、查詢表中重複資料。select * from people
where peopleId in (select   peopleId   from   people   group   by   peopleId   having   count(peopleId) > 1)

2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（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、尋找表中多餘的重複記錄（多個欄位）

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

4、刪除表中多餘的重複記錄（多個欄位），只留有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)

(二)
比方說
在A表中存在一個欄位“name”，
而且不同記錄之間的“name”值有可能會相同，
現在就是需要查詢出在該表中的各記錄之間，“name”值存在重複的項；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果還查性別也相同大則如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(三)
方法一

declare @max integer,@id integer

declare cur_rows cursor local for select 主欄位,count(*) from 表名 group by 主欄位 having count(*) >； 1

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

delete from 表名 where 主欄位 = @id

fetch cur_rows into @id,@max

end

close cur_rows

set rowcount 0

方法二

＂重複記錄＂有兩個意義上的重複記錄，一是完全重複的記錄，也即所有欄位均重複的記錄，二是部分關鍵字段重複的記錄，比如Name欄位重複，而其他欄位不一定重複或都重複可以忽略。

　　1、對於第一種重複，比較容易解決，使用

select distinct * from tableName

　　就可以得到無重複記錄的結果集。

　　如果該表需要重複資料刪除的記錄（重複記錄保留1條），可以按以下方法刪除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

　　發生這種重複的原因是表設計不周產生的，增加唯一索引列即可解決。

　　2、這類重複問題通常要求保留重複記錄中的第一條記錄，操作方法如下

　　假設有重複的欄位為Name,Address，要求得到這兩個欄位唯一的結果集

select identity(int,1,1) as autoID, * into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

　　最後一個select即得到了Name，Address不重複的結果集（但多了一個autoID欄位，實際寫時可以寫在select子句中省去此列）

(四)
查詢重複

select * from tablename where id in (select id from tablename group by id

having count(id) > 1）