DataBase -- SQL INNER JOIN

SQL INNER JOIN 关键字

在表中存在至少一个匹配时，INNER JOIN关键字返回行。

INNER JIN关键字语法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name 

 

Question:

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  |
+----+------------------+

 Analysis:

 写一个SQL语句删除所有包含重复的Email的实例，只依据他们id的大小保留具有最小id的那个。

注意：这里是删除，因此要用delete而不是select；只保留最小id的那个，因此只删除当前id比前面id大的那个。（这就是左右的限制条件）

 

Answer：

delete p1 from Person p1 
inner join Person p2 
where p1.Email = p2.Email and p1.Id > p2.Id