【数据库】排名问题
创始人
2024-05-29 15:47:53
0

  • 返回第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;

相关内容

热门资讯

中证A500ETF摩根(560... 8月22日,截止午间收盘,中证A500ETF摩根(560530)涨1.19%,报1.106元,成交额...
A500ETF易方达(1593... 8月22日,截止午间收盘,A500ETF易方达(159361)涨1.28%,报1.104元,成交额1...
何小鹏斥资约2.5亿港元增持小... 每经记者|孙磊    每经编辑|裴健如 8月21日晚间,小鹏汽车发布公告称,公司联...
中证500ETF基金(1593... 8月22日,截止午间收盘,中证500ETF基金(159337)涨0.94%,报1.509元,成交额2...
中证A500ETF华安(159... 8月22日,截止午间收盘,中证A500ETF华安(159359)涨1.15%,报1.139元,成交额...
科创AIETF(588790)... 8月22日,截止午间收盘,科创AIETF(588790)涨4.83%,报0.760元,成交额6.98...
创业板50ETF嘉实(1593... 8月22日,截止午间收盘,创业板50ETF嘉实(159373)涨2.61%,报1.296元,成交额1...
港股异动丨航空股大幅走低 中国... 港股航空股大幅下跌,其中,中国国航跌近7%表现最弱,中国东方航空跌近5%,中国南方航空跌超3%,美兰...
电网设备ETF(159326)... 8月22日,截止午间收盘,电网设备ETF(159326)跌0.25%,报1.198元,成交额409....
红利ETF国企(530880)... 8月22日,截止午间收盘,红利ETF国企(530880)跌0.67%,报1.034元,成交额29.0...