SQL 问题汇总

6种方案诠释MySQL通用查询策略

175. 组合两个表

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

176. 第二高的薪水

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

177. 第N高的薪水

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

注意两点：

1. limit 1 offset M与limit M, 1 等价
2. declare M int; set M:=N-1;要写在BEGIN和RETURN之间

178. 分数排名

好理解的做法：

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 Score DESC;

新开一列，用select count(distinct b.Score) from Scores b where b.Score >=a.Score表示序号

窗口函数做法：

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

窗口函数：

• rank
• dense_rank
• row_number

图解SQL面试题：经典排名问题