MySQL第N高的薪水
mysql 薪水
2023-09-11 14:14:56 时间
MySQL第N高的薪水
MySQL通用查询策略
排名是数据库中的一个经典题目,实际上又根据排名的具体细节可分为3种场景:
- 连续排名,例如薪水3000、2000、2000、1000排名结果为1-2-3-4,体现同薪不同名,排名类似于编号
- 同薪同名但总排名不连续,例如同样的薪水分布,排名结果为1-2-2-4
- 同薪同名且总排名连续,同样的薪水排名结果为1-2-2-3
不同的应用场景可能需要不同的排名结果,也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果,且是全局排名,不存在分组的问题,实际上还要相对简单一些。
值得一提的是:在Oracle等数据库中有窗口函数,可非常容易实现这些需求,而MySQL直到8.0版本也引入相关函数。最新OJ环境已更新至8.0版本,可直接使用窗口函数。
思路1:单表查询
由于本题不存在分组排序,只需返回全局第N高的一个,所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节:
1、同薪同名且不跳级的问题,解决办法是用group by按薪水分组后再order by
2、排名第N高意味着要跳过N-1个薪水,由于无法直接用limit N-1,所以需先在函数开头处理N为N=N-1。
注:这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数(意味着0、负数、小数都不行)或者单一变量(意味着不能用表达式),也就是说想取一条,limit 2-1、limit 1.1这类的写法都是报错的。
注:这种解法形式最为简洁直观,但仅适用于查询全局排名问题,如果要求各分组的每个第N名,则该方法不适用;而且也不能处理存在重复值的情况。
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
查询效率
思路2:子查询
- 排名第N的薪水意味着该表中存在N-1个比其更高的薪水
- 注意这里的N-1个更高的薪水是指去重后的N-1个,实际对应人数可能不止N-1个
- 最后返回的薪水也应该去重,因为可能不止一个薪水排名第N
- 由于对于每个薪水的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的salary小于表2的salary
- 以表1的salary分组,统计表1中每个salary分组后对应表2中salary唯一值个数,即去重
- 限定步骤2中having 计数个数为N-1,即实现了该分组中表1salary排名为第N个
- 考虑N=1的特殊情形(特殊是因为N-1=0,计数要求为0),此时不存在满足条件的记录数,但仍需返回结果,所以连接用left join
- 如果仅查询薪水这一项值,那么不用left join当然也是可以的,只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号,所以一定不为空,用join即可。
注:个人认为无需考虑N<=0的情形,毕竟无实际意义。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
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
相关文章
- Mysql加锁过程详解(2)-关于mysql 幻读理解
- mysql绿色版制作
- 10 Best MySQL Client GUI Interface – Free Download
- 【MySQL高级】Mysql锁问题
- MySQL: 锁 lock
- mysql中utf8和utf8mb4区别
- Centos安装mysql并进行命令补全
- Mysql错误check the manual that corresponds to your MySQL server version for the right syntax
- 从零基础入门MySQL数据库基础课
- 【mysql问题】解决2003-Can‘t connect to MySQL server on ‘ ‘(10060“Unknown error“)
- hivemysql 中的join理解
- 01-深入理解mysql索引底层数据结构
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- Mysql 5.7源码编译启动 报error问题:The server quit without updating PID file (/data/data_mysql/mysql.pid).
- MySQL的字符编码体系(一)——数据存储编码
- SQLAlchemy之mysql使用手册
- eclipse连接MySQL遇到java.lang.ClassFormatError: Invalid pc in LineNumberTable in class file