這是我在leetcode上遇到的一個(gè)題目：
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+
| 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:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
一開(kāi)始我的想法實(shí)在來(lái)簡(jiǎn)單瘪贱，想直接用子查詢來(lái)進(jìn)行刪除：
delete from Person
where Id in (select * from (select Id from Person group by Email having count(Email)>1) a)
and Id not in (select * from (select min(Id) from Person group by Email having count(Email)>1) b)
好久沒(méi)寫(xiě)SQL蠢沿，發(fā)現(xiàn)我這個(gè)想法實(shí)在太幼稚了怒坯。
先來(lái)一發(fā)題目的討論
you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)
不能寫(xiě)表的時(shí)候進(jìn)行內(nèi)查詢引用义图。最后來(lái)一發(fā)比較好的答案：
delete p1 from Person p1,Person p2 where
p1.Email = p2.Email
and
p1.Id > p2.Id
