使用 CTE 刪除重複的行
員工表:
| ID | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| 1 | Mark | Hastings | Male | 60000 |
| 1 | Mark | Hastings | Male | 60000 |
| 2 | Mary | Lambeth | Female | 30000 |
| 2 | Mary | Lambeth | Female | 30000 |
| 3 | Ben | Hoskins | Male | 70000 |
| 3 | Ben | Hoskins | Male | 70000 |
| 3 | Ben | Hoskins | Male | 70000 |
+------+-----------+----------+--------+--------+
CTE(通用表格表示式):
WITH EmployeesCTE AS
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1
執行結果:
| ID | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| 1 | Mark | Hastings | Male | 60000 |
| 2 | Mary | Lambeth | Female | 30000 |
| 3 | Ben | Hoskins | Male | 70000 |
+------+-----------+----------+--------+--------+