说好的每日一题，但感觉leetcode上数据库的题有一百多道，每日一题进度有些慢，那就有时每日两三题吧。
而且好像在学习Leetcode176 第二高薪水的时候就可以把第N高薪水搞定了。
下面尝试一下：
题目
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
审题
好像没必要审题了，可能遇到的坑应该都很明确了。
自己的解答
SQL菜鸡表示还没在Mysql中写过定义函数
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
);
END
太年轻了。。。 先学习一下SQL中怎么创建函数吧
- 语法
CREATE FUNCTION func_name ( [func_parameter] ) //括号是必须的，参数是可选的
RETURNS type
[ characteristic ...] routine_body
- CREATE FUNCTION 用来创建函数的关键字；
- func_name 表示函数的名称；
- func_parameters为函数的参数列表，参数列表的形式为：[IN|OUT|INOUT] param_name type
IN：表示输入参数；
OUT：表示输出参数；
INOUT：表示既可以输入也可以输出；
param_name：表示参数的名称；
type：表示参数的类型，该类型可以是MySQL数据库中的任意类型；
RETURNS type：语句表示函数返回数据的类型；
characteristic: 指定存储函数的特性，取值与存储过程时相同，详细请访问－MySQL存储过程使用;
在MySQL——函数的使用方法与MySQL内部函数的使用方法一样。
差不多了解了创建函数的语法，下面来自己写一下
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT IFNULL(
(SELECT DISTINCT Salary AS getNthHighestSalary
FROM employee
ORDER BY Salary DESC
LIMIT 1 OFFSET N
),NULL)
);
END
额。。 还以为写对了 仔细检查发现 取第N个应该去掉的是前N-1个
但是把N改成N-1 直接报错了
在评论区看到
因为 mysql 中，limit 后面不能带运算符，只能是常量
解决方法: SET N = N - 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) AS getNthHighestSalary
);
END
通过了。
其他解法
6种方案诠释MySQL通用查询策略
值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。当前MySQL OJ系统为5.7版本（可通过编码区——语言选择按钮——左侧的"i"查看环境信息），所以不能直接应用。
初学Mysql应用的都是5.0的版本，8.0后引入的新特性也要在后续进行学习。
这里把之前的思路，和没有想到过的思路(引用如上连接)进行一个总结
1.单表查询
由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：
同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
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
去掉分组 用distinct去重也是可以的
2.子查询
1.排名第N的薪水意味着该表中存在N-1个比其更高的薪水
2.注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
3.最后返回的薪水也应该去重，因为可能不止一个薪水排名第N
4.由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
(SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
);
END
解释的挺好的，也很容易理解。
3.自连接
一般来说，能用子查询解决的问题也能用连接解决。
1.两表自连接，连接条件设定为表1的salary小于表2的salary
2.以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重
3.限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个
考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join
4.如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。
有点儿绕，但还是很好懂的。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
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
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路4:笛卡尔乘积
我感觉这个思路好像就是，对于联结sql92语法和sql99语法的两种不同形式。
可以很容易将思路2中的代码改为笛卡尔积连接形式，其执行过程实际上一致的，甚至MySQL执行时可能会优化成相同的查询语句。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e1.salary
FROM
employee e1, employee e2
WHERE
e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路5:自定义变量
没接触过自定义变量呀
先学习一下 mysql用户变量和set语句
SET @var_name = expr [, @var_name = expr] ...
SELECT @var_name := expr [, @var_name = expr] ...
以上方法2-4中均存在两表关联的问题，表中记录数少时尚可接受，当记录数量较大且无法建立合适索引时，实测速度会比较慢，用算法复杂度来形容大概是O(n^2)量级（实际还与索引有关）。那么，用下面的自定义变量的方法可实现O(2*n)量级，速度会快得多，且与索引无关。
1.自定义变量实现按薪水降序后的数据排名，同薪同名不跳级，即3000、2000、2000、1000排名后为1、2、2、3；
2.对带有排名信息的临时表二次筛选，得到排名为N的薪水；
3.因为薪水排名为N的记录可能不止1个，用distinct去重
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
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
);
END
分解代码理解一下:
#@r:=IF(@p=salary, @r, @r+1) AS rnk判断p变量是否发生变化 变化则rnk+1 不变依然为rnk
#@p:= salary 更新p变量
SELECT salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
#初始化(init) r为0 p为null
FROM employee, (SELECT @r:=0, @p:=NULL)init
查询结果是什么呢？
由于数据是升序的，所以得到这个结果。所以需要对salary加一个降序排序。
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
结果：
确实是我们想要的排序结果。
然后对这个结果进行子查询并去重,把rnk=N的选出来即可。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
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
);
END
思路6:窗口函数
你咋又来了。。 那就看看吧
实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：
row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用
另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是
partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
其实就相当于对内置函数的调用，想法与思路5是一致的。
..
