SQL查詢列(欄位)重複值及操作--整理

hunhun1122發表於2017-08-17

SQL查詢列(欄位)重複值及操作如下:

方法一:

1、查詢表中多餘的重複記錄,重複記錄是根據單個欄位(peopleId)來判斷
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、查詢表中多餘的重複記錄(多個欄位)
select * 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)

 

方法二:

現在假設資料庫表名為student,裡面有欄位Sno(學號),ID(身份證),需要查詢Sno欄位重複的記錄,ID欄位是唯一的   這裡提供兩種查詢的方法:

 

    1:通過學號和身份證欄位來查詢(資料庫執行效率高,推薦)

    SELECT *
    FROM student AS A
    WHERE (Sno IN (SELECT Sno FROM
student AS B WHERE A.ID <> B.ID))

    2:只通過學號欄位來查詢(資料庫執行效率低,不推薦)

    SELECT *
    FROM student AS A
    WHERE (SELECT COUNT(*) FROM
student WHERE Sno=A.Sno)>1

    從執行結果來看,1和2相同,但是執行效率完全不同,方法一執行一萬多條資料只需要幾秒鐘即可,方法二則需要好幾分鐘,而且還有當機現象。

 

方法三:

有兩個意義上的重複記錄,一是完全重複的記錄,也即所有欄位均重複的記錄,二是部分關鍵欄位重複的記錄,比如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子句中省去此列)

 

補充:

有兩個以上的重複記錄,一是完全重複的記錄,也即所有欄位均重複的記錄,二是部分關鍵欄位重複的記錄,比如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子句中省去此列)

相關文章