首页 > 数据库技术 > 详细

DataBase -- SQL INNER JOIN

时间:2016-03-05 11:37:48      阅读:174      评论:0      收藏:0      [点我收藏+]

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

 

 

 

 

DataBase -- SQL INNER JOIN

原文:http://www.cnblogs.com/little-YTMM/p/5244351.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!