Thursday, June 15, 2017

SQL: 去除重覆記錄但保留一行

在SQL中有時會有重覆記錄,希望去除某兩個欄位重覆的整筆記錄可以用以下方法。
其中當c1和c2在tableName重覆時,可以去除其他有相同值的記錄。

WITH cte AS (
  SELECT c1, c2,
     row_number() OVER(PARTITION BY c1, c2 ORDER BY c1) AS [rn]
  FROM tableName
)
delete cte WHERE [rn] > 1

No comments: