Sql-Server

如何從具有重複鍵的表中刪除唯一行?

  • December 18, 2020

我有以下數據:

     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;

引用自:https://dba.stackexchange.com/questions/62272