Leetcode sql刷题记录2: 182、183、196

坚持每天做3题

我用的是mysql语言

182.查找重复的电子邮箱

三种思路，第一种，建立子查询，查出每个电子邮箱出现的次数，筛选出大于1的；第二种，用group by,having count（where后面不能加聚合函数）；第三种，用自联结，找出拥有相同邮箱，但id不相同的

解法1：

select email

from

(select Email, count(*) as num

from person

group by email) as count_table

where num > 1;

解法2：

select email 

from person

group by email 

having count(email) >1;

解法3：

select distinct p1.email

from email as p1 join email as p2

on p1.email = p2.email

             and p1.email != p2.email

183.从不订购的客户

两种方法，一种是左联结，找出表order中与表customer中无法对应起来的id；第二种用not in，找出没有在表orders里面出现过的id

解法1：

用左联结

select c.name as 'customers'

from customers as c left join orders as o

on c.id = o.customerid

where o.id is null

解法2：

select c.name as 'customers'

from customers as c

where c.id not in (select customerid from orders)

196. 删除重复的电子邮箱

182的换汤不换药版本，用182的解法3即可轻松达成

解法：

delete p1

from person as p1, person p2

where p1.Email = p2.Email

and p1.Id > p2.Id