leetcode数据库中等题目

176. 第二高的薪水

select ifnull((select distinct Salary from Employee order by Salary desc limit 1, 1), null) as SecondHighestSalary 

子查询去重查找第二高的薪水，如果为空用ifnull的第二个参数（null），查询结果起别名

177. 第N高的薪水

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 N, 1), null) as getNthHighestSalary
  );
END

参数N-1 再和上面一样进行查询

178. 分数排名

select a.Score, count(distinct(b.Score)) as 'Rank'
from Scores a join Scores b 
on b.Score >= a.Score 
group by a.Id 
order by a.Score desc;

连表查询，查出去重后b中分数大于等于a的条数进行计数，用a.id进行分组保证结果条数

180. 连续出现的数字

select distinct l.Num as ConsecutiveNums from Logs l join Logs o join Logs g on (l.id = o.id - 1 and l.Num = o.Num) and (o.id = g.id - 1 and o.Num = g.Num) 

连接查询三次，比较相邻的id的Num，三个连续相同的就查询出来，将结果去重保留一个

184. 部门工资最高的员工

select d.Name as 'Department', e.Name as 'Employee', e.Salary as 'Salary' from Employee e join Department d on e.DepartmentId = d.Id and (e.DepartmentId,e.Salary) in (select DepartmentId, max(Salary) from Employee group by DepartmentId)

使用子查询按部门id分组查询出每个部门中最高的工资，查询出表中雇员是否是对应部门最高的工资

626. 换座位

select s1.id as 'id', coalesce(s2.student, s1.student) as 'student' from seat s1 left join seat s2 on (s1.id = s2.id - 1 and s1.id % 2 = 1) or (s2.id = s1.id - 1 and s2.id % 2 = 1)