LeetCode-SQL-175-178

175-组合两个表

select FirstName,LastName,City,State
from Person p
left join Address using(personId)

176-第二高的薪水

select distinct salary from Employee order by salary desc limit 1,1 
# 但是这样的话，当第二高薪水为null时，返回不了
# 通过临时表的方式输出null
select(
    select distinct salary # 薪资重复的都只算一个
    from Employee
    order by salary desc
    limit 1,1
) SecondHighestSalary

177-第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
# 由于limit中不能运算，则在这里进行计算
set N = N-1;
  RETURN (
    select distinct salary
    from Employee
    order by salary desc
    limit N,1
  );
END

178-分数排名

即rank字段应该是连续的
两个rank函数：
rank() over(排序逻辑)：排名不连续
dense_rank() over(排序逻辑)：排名连续

#使用dense_rank() over(排序逻辑)函数
select score, 
	dense_rank() over (order by score desc) as 'rank'
from scores