leetcode|leetcode 数据库练习 leetcode|sql

【leetcode|leetcode 数据库练习】183. Customers Who Never Order
在order表中找出没有下单的customer

select C.name as Customers from Customers C where Id not in (select distinct O.CustomerId from Orders O)

先用子查询调出所有下单的id，然后再在customers表中取差
这里不能用distinct，虽然逻辑上是对的，但是加上会错

select tmp.name as Customers from (select C.Id,C.Name,O.Id flag from Customers C left join Orders O on O.CustomerId=C.Id) tmp where tmp.flag is null

也可以用连接.首先用id左连接将customers和orders连接,然后在连接表上找为空的下单记录.这里左连接是因为有的人没下单,然后要保证所有客户都出现在表中
182. Duplicate Emails
找出重复邮件的名字

select Email from (select Email,count(Email) num from Person group by Email) tmp where tmp.num>1

先自己建一个名字与数量的临时表，在临时表中找重复的名字
175. Combine Two Tables
provides the following information for each person in the Person table, regardless if there is an address for each of those people:

select P.FirstName,P.LastName,A.City,A.State from Person P left join Address A on P.PersonId=A.PersonId

由于不论有没有地址都要显示，所以采用左连接.
596. Classes More Than 5 Students
找出所有学生数>=5的课程

select class from (select class,count(distinct student) num from courses group by class) tmp where tmp.num>=5

这个题和182. Duplicate Emails很像，都是建立变量与数量关系，在这张临时表里查找.但是不同点在于,这里的记录可能重复,所以在计数时要去重

select class from courses group by class having count(distinct student)>=5

还有简单一点的方法,直接对查询结果分组,统计不同学生个数
181. Employees Earning More Than Their Managers
找出比自己经理工资高的员工

select Name as Employee from Employee E join (select Id,Salary as boss from Employee) as manager on E.ManagerId=manager.Id where E.Salary>manager.boss

连接查询

select A.Name as Employee from Employee as A,Employee as B where A.ManagerId=B.Id and A.Salary>B.Salary

笛卡尔积
180. Consecutive Numbers
在Logs表中找Num相同,id连续,长度为3的Num

select distinct F.num as ConsecutiveNums from Logs as F,Logs as S,Logs as T where F.Id+1=S.Id and S.Id+1=T.Id and F.Num=S.Num and S.Num=T.Num

毫无优美的笛卡尔积
1179. Reformat Department Table

select id, max(case when month = 'Jan' then revenue end) Jan_Revenue, max(case when month = 'Feb' then revenue end) Feb_Revenue, max(case when month = 'Mar' then revenue end) Mar_Revenue, max(case when month = 'Apr' then revenue end) Apr_Revenue, max(case when month = 'May' then revenue end) May_Revenue, max(case when month = 'Jun' then revenue end) Jun_Revenue, max(case when month = 'Jul' then revenue end) Jul_Revenue, max(case when month = 'Aug' then revenue end) Aug_Revenue, max(case when month = 'Sep' then revenue end) Sep_Revenue, max(case when month = 'Oct' then revenue end) Oct_Revenue, max(case when month = 'Nov' then revenue end) Nov_Revenue, max(case when month = 'Dec' then revenue end) Dec_Revenue from Department group by id

627. Swap Salary
只用一条update，不建立中间表，交换表中性别