leetcode 刷题

176：第二高的薪水

select (select distinct salary from employee order by salary limit 1 offset 1) as secondhighestsalary;  ---去掉第一个，再从第一个开始

177：第N高的薪水

------相关子查询：子查询中引用了外层查询所引用表的字段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

  RETURN (

      # Write your MySQL query statement below.

    select max(salary) from employee e1

      where N=(select count(distinct salary) from employee e2

              where e2.salary >=e1.salary)

  );

END
----执行步骤，1.从父查询中取出每一条salary，与子查询中的e2.salary进行比较，直到当大于e2.salary的记录等于n
            2.父查询输出最大的salary

https://blog.csdn.net/mascf/article/details/50288199

178. 分数排名

---相关子查询
---方法一
select score,(select count(distinct score) from scores e1 where e1.score>= e2.score) rank

from scores e2

order by rank;

180. 连续出现的数字

select distinct l1.num as consecutivenums

from logs as l1 inner join logs as l2

on l1.id=l2.id-1

inner join logs as l3

on l2.id=l3.id-1

where l1.num=l2.num and l2.num=l3.num;