心得
題目要求刪除同樣Email
的資料,在DELETE
的條件WHERE
裡面不能包含異動的表(Person
),所以這裡再包了一層SELECT
來排除編譯錯誤的問題,而這層必須給予別名(強制
)否則會編譯錯誤。
問題
Write a SQL query to delete all duplicate email entries in a table named
Person
, keeping only unique emails based on its smallest Id.
12345678 +----+------------------+| Id | Email |+----+------------------+| 1 | john@example.com || 2 | bob@example.com || 3 | john@example.com |+----+------------------+Id is the primary key column for this table.For example, after running your query, the above
Person
table should have the following rows:
123456 +----+------------------+| Id | Email |+----+------------------+| 1 | john@example.com || 2 | bob@example.com |+----+------------------+
答案
1 2 3 4 5 6 7 |
DELETE FROM Person WHERE Id NOT IN (SELECT y.* FROM ( SELECT MIN(z.Id) FROM Person AS z GROUP BY z.Email) AS y) |