每日一SQL-刪除重複資料

2010-07-13

126374
0
SQL語法

每日一SQL-刪除重複資料

P.S 我發完才發現七點多已經有人發了同樣的題目，是因為同一個苦主嗎??ㄎㄎ..

我的每日一SQL快要變成每月一SQL了…

今天又遇到一個問題，有個髒髒的資料庫，裡面有很多筆重複的資料

例如

要篩選出資料但不重複很簡單，就 Select distinct 欄位 from Table名稱就行了

可以看到實際上的資料只有四筆，假設這張table沒有跟其他的table有關連

那我要如何刪掉重複的資料只保留一筆呢?

先試著留下一筆資料

  Select * From [Product] Where ID In (Select Max(ID) From [Product] Group By 產品名稱)

應該很直覺吧，就是先將資料Group by一個重複的欄位，然後篩出主索引鍵

然後用原table where in 這些資料，就可以篩出來了。

刪除的話，就是顛倒條件

  DELETE Product where ID NOT IN (Select Max(ID) From [Product] Group By 產品名稱)

就行囉。

小提醒：刪除資料前最好先簡單備份一下比較保險喔

快速語法 SELECT * INTO 新的table名 FROM 來源table

假設今天這table連主索引鍵都沒有。如這樣

怎辦呢? 沒差，利用ROW_NUMBER() + CTE 創一個欄位給他就行了

with temp as(
SELECT *, ROW_NUMBER() over(order by 產品名稱) as rnk
FROM [MY].[dbo].[Product]
)
  
select * from temp 
where rnk IN
(SELECT Max(rnk) FROM temp GROUP BY 產品名稱)

刪除就是把第6行~第8行的SELECT換成

DELETE temp where rnk NOT IN (Select Max(rnk) From temp Group By 產品名稱)

就行啦。

再假設今天的Table雖然有重複資料，但不是每個欄位的資料都重複，如

那我想只想留同一品名價格最大的話

一樣是

with temp as(
SELECT *, ROW_NUMBER() over(order by 單價) as rnk
FROM [MY].[dbo].[Product]
)
  
select * from temp 
where rnk IN
(SELECT Max(rnk) FROM temp  GROUP BY 產品名稱)

刪除就跟上面的寫法一樣囉。

心得：CTE真好用 :)

回首頁