Leetcode刷题：MySQL

一、理论知识

二、常用SQL语句

1、联合查询（left join是left outer join的简写）

左连接： select 字段1，字段2... fron Table1 left join Table2 where Table1.id=Table2.id;

右连接：select 字段1，字段2... fron Table1 right join Table2 where Table1.id=Table2.id;

内连：select 字段1，字段2... fron Table1 inner join Table2 where Table1.id=Table2.id;

limit y offset x：表示查询结果跳过 x 条数据，读取前 y 条数据，注意limit和offset字段后面只接受正整数或者单一变量（不能用表达式）。

三、题目

select FirstName,LastName,City,State from Person left outer join Address on Person.PersonId=Address.PersonId;

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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N:=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select 
            Salary 
        from 
            Employee 
        group by Salary 
        order by Salary desc 
        limit N,1
  );
END

思路： 第一部分是降序排列的分数，第二部分是根据去重之后大于等于该分数的个数确定排名。

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score>=a.Score) as `Rank`
from Scores a
order by a.Score DESC