数据库——SQL语句实战

1. 176. 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
例如下述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+----+--------+		+---------------------+
| Id | Salary |		| SecondHighestSalary |
+----+--------+		+---------------------+
| 1  | 100    |		| 200                 |
| 2  | 200    |		+---------------------+
| 3  | 300    |
+----+--------+

• 方法1：使用排序

不考虑返回null：distinct去重、order by排序、desc降序、limit限制返回记录数（第一个参数：行的偏移量（从0开始，0表示第一个），第二个参数：行数）

select distinct salary as SecondHighestSalary 
from Employee 
order by salary desc limit 1,1 

考虑返回null：使用临时表、使用IFNULL

# limit 2,1 等价于 limit 1 offset 2
select (
	select distinct salary 
	from Employee 
	order by salary desc limit 1,1
) as SecondHighestSalary

select IFNULL((
	select distinct salary 
	from Employee 
	order by salary desc limit 1 offset 1),NULL) 
as SecondHighestSalary

• 方法2：使用max

#找到最大的
select max(distinct salary) from Employee
#找到第二大的
select max(distinct salary) from Employee where salary <(select max(distinct salary) from Employee
#
select IFNULL((select max(distinct salary) from Employee where salary <(select max(distinct salary) from Employee)),NULL) as SecondHighestSalary

2. 177. 第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

• 思路1

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
    RETURN (
        # Write your MySQL query statement below.
        select IFNULL((select distinct salary from Employee order by salary desc limit 1 offset N),NULL)
    );
END

• 思路2：求第n高，即有n-1个更高的，使用where条件、join、笛卡尔积

SELECT DISTINCT e.salary 
FROM employee e
WHERE (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1

SELECT DISTINCT e1.salary
FROM employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
GROUP BY e1.salary
HAVING count(DISTINCT e2.salary) = N-1

SELECT DISTINCT e1.salary
FROM employee e1, employee e2 
WHERE e1.salary <= e2.salary
GROUP BY e1.salary
HAVING count(DISTINCT e2.salary) = N

• 思路3：自定义变量（SELECT语句：=是赋值，=是比较）

SELECT DISTINCT salary 
FROM (
	SELECT salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
    FROM  employee, (SELECT @r:=0, @p:=NULL)init 
    ORDER BY salary DESC
) tmp
WHERE rnk = N

• 总结
  • 能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高
  • 不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果
  • 能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证
  • 自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现
  • 如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。

3. 178. 分数排名

Scores 表：查询结果按分数从高到低排列
+----+-------+			+-------+------+
| Id | Score |			| Score | MyRank |
+----+-------+			+-------+------+
| 1  | 3.50  |			| 4.00  | 1    |
| 2  | 3.65  |			| 4.00  | 1    |
| 3  | 4.00  |			| 3.85  | 2    |
| 4  | 3.85  |			| 3.65  | 3    |
| 5  | 4.00  |			| 3.65  | 3    |
| 6  | 3.65  |			| 3.50  | 4    |
+----+-------+			+-------+------+

思路：

# 第一列
select a.Score as Score from Scores a order by a.Score DESC
# 第二列
select count(distinct b.Score) from Scores b where b.Score >= X as MyRank 
# 合并
select 
	a.Score as Score,
	(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as MyRank 
from Scores a
order by a.Score DESC
# 或者
select s1.Score,count(distinct(s2.score)) MyRank
from Scores s1,Scores s2
where s1.score<=s2.score
group by s1.Id
order by MyRank
# 或者
select s1.Score,count(distinct(s2.score)) MyRank 
from Scores s1 join Scores s2 
on s1.score<=s2.score 
group by s1.Id 
order by s1.Score desc;

问题：相当于2遍select，慢

优化：使用内部变量

SELECT Score, 
	CAST(CASE
	WHEN @pre = Score THEN @rk + 0 # 这里的=是比较运算符
	WHEN @pre := Score THEN @rk := @rk + 1
	ELSE @rk := @rk + 1
	END AS SIGNED)
	AS MyRank 
FROM Scores, (SELECT @pre := NULL, @rk := 0) AS init # 变量初始化
ORDER BY Score DESC;

优化：使用窗口函数（MySQL8.0以上版本）

SELECT Score, 
	# rank() OVER(ORDER BY Score DESC) AS MyRank 
    DENSE_RANK() OVER(ORDER BY Score DESC) AS MyRank 
    # row_number() OVER(ORDER BY Score DESC) AS MyRank 
FROM Scores

使用rank()					使用DENSE_RANK()				使用row_number()
+-------+------+			+-------+------+			+-------+------+
| Score | Rank |			| Score | Rank |			| Score | Rank |
+-------+------+			+-------+------+			+-------+------+
| 4.00  | 1    |			| 4.00  | 1    |			| 4.00  | 1    |
| 4.00  | 1    |			| 4.00  | 1    |			| 4.00  | 2    |
| 3.85  | 3    |			| 3.85  | 2    |			| 3.85  | 3    |
| 3.65  | 4    |			| 3.65  | 3    |			| 3.65  | 4    |
| 3.65  | 4    |			| 3.65  | 3    |			| 3.65  | 5    |
| 3.50  | 6    |			| 3.50  | 4    |			| 3.50  | 6    |
+-------+------+			+-------+------+			+-------+------+