leetcode sql 刷题记录

176.查找第二高的薪水纪录
Write a SQL query to get the second highest salary from the Employee table.
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+

输出例子
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

select ifnull
((select salary  from employee
group by salary
order by salary desc
limit 1 offset 1),null)
as SecondHighestSalary

ifnull（，null）如果逗号前面是null就输出null，题目要求第二高为空输出null而不是空

177.提交第n高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
BEGIN    
  set N=N-1;  
  IF N < 0 
  THEN  RETURN NULL;  
  else  RETURN (      
  # Write your MySQL query statement below.     
  select ifnull( (select distinct salary       
  from employee order by salary desc       
  limit N,1),null)  );  
  end if;
  END

难点就在于不能limit n-1，1，要提前set n=n-1，主义严谨的地方n是从0开始的，所以有if n<0,最后也不能忘了end if。然后记得每一个完整的语句后面加分号

178.排名
mysql8.0以上直接用窗口函数，非常方便
窗口函数参考.

select score,
dense_rank() over (order by score desc) as rank
from Scores s 

LeetCode不支持8.0以上版本，考虑复表，也就是自己写rank()

select s1.score,count(distinct s2.score) as 'rank'
from scores s1,scores s2
where  s1.score<=s2.score
group by s1.id
order by score desc 

一定要按照s1.id 分类，才能数出比我大的有几个，不知道为什么leetcode里按照rank order by输不出

今天好累，明天继续2020-05-03

180.输出至少连续出现三次以上的数字
Write a SQL query to find all numbers that appear at least three times consecutively.
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+

前面要加distinct，因为可能会有多条记录，但是是同一数字

select  distinct l1.num from 
Logs l1,logs l2,logs l3
where l1.id+1=l2.id and l2.id+1=l3.id
and l1.num=l2.num and l2.num=l3.num

181.收入超过经理的员工
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
±—±------±-------±----------+
| Id | Name | Salary | ManagerId |
±—±------±-------±----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
±—±------±-------±----------+

select e1.name as 'Employee'
 from employee e1,employee e2
 where e1.salary>e2.salary
 and e2.id=e1.managerid

183.从不订购的用户

方法一：not in

select  c.name