【数据库】排名问题
- 返回第N高的一个
- 解决思路
- 返回N组中的第N高
- 解决思路
- 分数排名
- 解决思路
- 窗口函数
数据库经常被用来解决排名问题。
返回第N高的一个
单表查询:
表: Employee+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。来源：力扣（LeetCode） https://leetcode.cn/problems/nth-highest-salary
解决思路
使用order by 排序加limit限制。
注意：limit字段只接受正整数，不接受表达式
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINSET N := N-1;RETURN (# Write your MySQL query statement below.SELECT salaryFROM employeeGROUP BY salaryORDER BY salary DESCLIMIT N, 1);
END
返回N组中的第N高
表： Employee+--------------+---------+
| 列名 | 类型 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。表： Department+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。示例 1:输入：
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
输出：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
解决思路
# Write your MySQL query statement below
select Department.Name as Department,Employee.name as Employee,Employee.Salary
from Employee,Department
where Employee.departmentId = Department.id
and (Employee.salary,Employee.departmentId)
in (select max(salary),departmentId from Employee group by departmentId);
分数排名
表: Scores+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。编写 SQL 查询对分数进行排序。排名按以下规则计算:分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。查询结果格式如下所示。示例 1:输入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
输出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
解决思路
窗口函数
窗口函数的使用方法（rank()，dense_rank()，row_number()为专用窗口函数）：
select *,rank() over (order by 成绩 desc) as ranking,dense_rank() over (order by 成绩 desc) as dese_rank,row_number() over (order by 成绩 desc) as row_num
from 班级表
其区别是：
# Write your MySQL query statement below
select score,dense_rank() over (order by score desc) as 'rank'
from scores;