Sql-Server
如何從具有重複鍵的表中刪除唯一行?
我有以下數據:
road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ... | MAIN ST | 2 | ... | ... | MAIN ST | 3 | ... | ... | BERRY LN | 4 | ... | ... | BERRY LN | 5 | ... | ... | BERRY LN | 6 | ... | ... | JOHN RD | 7 | ... | ... | JOHN RD | 8 | ... | ... |
我希望刪除道路列中所有重複的行。刪除哪些重複項對我來說並不重要。最終結果可能如下所示:
road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 3 | ... | ... | BERRY LN | 4 | ... | ... | JOHN RD | 8 | ... | ... |
只要道路列是唯一的,id 是任何值。
有沒有一種簡單的方法可以在 SQL 或 T/SQL 中做到這一點?我遵循了此處的指南,但它僅適用於具有非唯一行的重複鍵。
通過使用ROW_NUMBER()排名函式,您可以實現這一點。下面是一個例子。前兩個查詢僅用於數據驗證。我堅信您應該在實際刪除數據之前查看將要刪除的內容(以及將保留的內容)。
(驗證)將被刪除的行
;with cte as ( select *, row_num = row_number() over (partition by road order by ID) from dbo.YourTable ) select * from cte where row_num > 1;
(驗證)不會被刪除的行
;with cte as ( select *, row_num = row_number() over (partition by road order by ID) from dbo.YourTable ) select * from cte where row_num = 1;
數據刪除
注意:下面的查詢,當修改以適應您的環境時,實際上會刪除 data。因此,您應該確保您確實要刪除數據,並且在執行任何數據修改之前還要備份數據/數據庫。
;with cte as ( select *, row_num = row_number() over (partition by road order by ID) from dbo.YourTable ) delete from cte where row_num > 1;