中等

177. 第N高的薪水

1
2
3
4
5
6
7
8
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset N),null) as getNthHighestSalary
  );
END
  • 函数的定义，关键字create function，begin开始，end结束

178. 分数排名

1
select Score,dense_rank() over(order by Score desc) as `Rank` from Scores;
  • 窗口函数dense_rank()：两个分数相同，则排序相同，下一个分数的排序是下一个连续的整数值，不空出并列所占的位次
  • rank()：两个分数相同，则排序相同，下一个分数的排序需空出并列所占的位次
  • row_number()：为行分配序号，不考虑是否并列，即使数值相同也会连续排序
  • over()：排序函数没有参数，必须通过over()语句中的order by column_name指定按照某列排序，还可通过over()语句中的partition by column_name指定按照某列分组

180. 连续出现的数字

1
2
3
4
select distinct Num as ConsecutiveNums from(
    select id,Num,lag(Num,1) over() as p1,lead(Num,1) over() as p2 from Logs
) as temp
 where Num=p1 and Num=p2;
  • 窗口函数lag(expr,offset) over()和lead(expr,offset) over()：分别返回当前行之前和之后的行，offset指定向前或向后滑动几行

184. 部门工资最高的员工

1
2
3
4
5
select Department,Employee,Salary from(
    select Department.Name as Department,Employee.Name as Employee,Salary,
    rank() over(partition by Employee.DepartmentId order by Salary desc) as rk
    from Employee inner join Department on Employee.DepartmentId=Department.Id) as temp
where rk=1;

626. 换座位

1
2
3
4
select id,
case when id%2 = 1 then lead(student,1,student) over()
when id%2 = 0 then lag(student,1) over() end as student
from seat;

困难

185. 部门工资前三高的所有员工

1
2
3
4
5
select Department,Employee,Salary from (
    select Department.Name as Department,Employee.Name as Employee,Salary,
    dense_rank() over(partition by Employee.DepartmentId order by Salary desc) as rk
    from Employee inner join Department on Employee.DepartmentId=Department.Id) as temp
where rk<=3;
  • 与184题“部门工资最高的员工”不同，不能使用rank()而应该使用dense_rank()

262. 行程和用户




1
2
3
4
5
select Request_at as `Day`,round(sum(if(Status='completed',0,1))/count(*),2) as 'Cancellation Rate' from Trips
where Request_at between '2013-10-01' and '2013-10-03' and
Client_Id in (select Users_Id from Users where Banned='No') and
Driver_Id in (select Users_Id from Users where Banned='No')
group by Request_at;
  • round()函数：四舍五入保留指定位数的小数

601. 体育馆的人流量


1
2
3
4
5
6
select distinct a.* from Stadium as a,Stadium as b,Stadium as c where
a.people >= 100 and b.people >= 100 and c.people >= 100 and (
    (a.id = b.id -1 and b.id = c.id - 1) or
    (a.id = b.id - 1 and a.id = c.id + 1) or
    (a.id = b.id + 1 and b.id = c.id + 1)
) order by visit_date;