175.组合两个表

1
select FirstName,LastName,City,State from Person left join Address on Person.PersonID=Address.PersonID;
  • 知识点：表联结
  • 不能用where Person.PersonId=Address.PersonId，因为where相当于inner join，无法匹配到地址为null的记录

176. 第二高的薪水

1
select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1),null) as SecondHighestSalary;
  • 知识点：判断空值、去重、排序、指定返回的行数及起始位置
  • ifnull(x,y)：若x不为空则返回x，否则返回y

181. 超过经理收入的员工

1
select e1.Name as Employee from Employee as e1,Employee as e2 where e1.ManagerId=e2.Id and e1.Salary > e2.Salary;
  • 知识点：通过给同一个表起不同的别名进行关联查询

182. 查找重复的电子邮箱

1
select Email from Person group by Email having count(Email) > 1;
  • 知识点：分组、过滤、聚合函数
  • where对行进行过滤，having对组进行过滤

183. 从不订购的客户

1
select Name as Customers from Customers where Id not in (select CustomerId from Orders);
  • 知识点：子查询

196. 删除重复的电子邮箱

1
delete from Person where Id not in (select * from (select min(Id) from Person group by Email) as SubPerson);
  • 知识点：子查询、表更新
  • select和delete不能作用在同一个表，select查询获得的中间表必须设置别名才能用于delete

197. 上升的温度

1
select w1.id from Weather as w1,Weather as w2 where datediff(w1.recordDate,w2.recordDate) = 1 and w1.Temperature > w2.Temperature;
  • 知识点：给同一个表起不同的别名、datediff
  • datediff(date1,date2)：date1与date2相差的天数，date1早于date2得到的是负数，date1晚于date2得到的是正数

595. 大的国家

1
select name,population,area from World where area > 3000000 or population > 25000000;

596. 超过5名学生的课

1
select class from courses group by class having count(distinct student) >= 5;
  • 题目有坑，考虑有学生重修的情况，不能省略distinct

620. 有趣的电影

1
select * from cinema where not description = 'boring' and id%2 = 1 order by rating desc;
  • not可以放在要过滤的列前面

627. 变更性别

1
update salary set sex = case sex when 'f' then 'm' else 'f' end;
  • 知识点：case when语句

1179. 重新格式化部门表


1
2
3
4
5
6
7
8
9
10
11
12
13
14
select id,
    sum(if(month='Jan',revenue,null)) as Jan_Revenue,
    sum(if(month='Feb',revenue,null)) as Feb_Revenue,
    sum(if(month='Mar',revenue,null)) as Mar_Revenue,
    sum(if(month='Apr',revenue,null)) as Apr_Revenue,
    sum(if(month='May',revenue,null)) as May_Revenue,
    sum(if(month='Jun',revenue,null)) as Jun_Revenue,
    sum(if(month='Jul',revenue,null)) as Jul_Revenue,
    sum(if(month='Aug',revenue,null)) as Aug_Revenue,
    sum(if(month='Sep',revenue,null)) as Sep_Revenue,
    sum(if(month='Oct',revenue,null)) as Oct_Revenue,
    sum(if(month='Nov',revenue,null)) as Nov_Revenue,
    sum(if(month='Dec',revenue,null)) as Dec_Revenue
from Department group by id;
  • 知识点：if语句