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