sql 练习题

问题描述：

1、从表中查出重复的邮箱

 

解：
select distinct a.Email from Person a, Person b where a.Email = b.Email and a.Id != b.Id

select Email from Person group by Email having count(Email) > 1

2、查询连续出现的数字

select L3.Num as ConsecutiveNums  from Logs L1 
left join Logs L2 on L1.Id+1=L2.Id and L1.Num=L2.Num 
left join Logs L3 on L2.Id+1=L3.Id and L2.Num=L3.Num 
where L3.Id is not null group by L3.Num

3、编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
delete p1 from Person p1 ,Person p2 where p1.Email =p2.Email and p1.Id > p2.Id