题目描述
(通过次数396 | 提交次数1,011,通过率39.17%)
表: Students +---------------+------+ | Column Name | Type | +---------------+------+ | student_id | int | | department_id | int | | mark | int | +---------------+------+ student_id 是该表的主键。 该表的每一行都表示一个学生的 ID,该学生就读的院系 ID,以及他们的考试分数。 编写一个 SQL 查询,以百分比的形式报告每个学生在其部门的排名,其中排名的百分比使用以下公式计算: (student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。percentage 应该四舍五入到小数点后两位。 student_rank_in_the_department由mark的降序决定,mark 最高的学生是 rank 1。如果两个学生得到相同的分数,他们也会得到相同的排名。 以 任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入: Students 表: +------------+---------------+------+ | student_id | department_id | mark | +------------+---------------+------+ | 2 | 2 | 650 | | 8 | 2 | 650 | | 7 | 1 | 920 | | 1 | 1 | 610 | | 3 | 1 | 530 | +------------+---------------+------+ 输出: +------------+---------------+------------+ | student_id | department_id | percentage | +------------+---------------+------------+ | 7 | 1 | 0.0 | | 1 | 1 | 50.0 | | 3 | 1 | 100.0 | | 2 | 2 | 0.0 | | 8 | 2 | 0.0 | +------------+---------------+------------+ 解释: 对于院系 1: - 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0 - 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0 - 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0 对于院系 2: - 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0 - 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/compute-the-rank-as-a-percentage
//测试数据 Create table If Not Exists Students (student_id int, department_id int, mark int); insert into Students (student_id, department_id, mark) values ('2', '2', '650'); insert into Students (student_id, department_id, mark) values ('8', '2', '650'); insert into Students (student_id, department_id, mark) values ('7', '1', '920'); insert into Students (student_id, department_id, mark) values ('1', '1', '610'); insert into Students (student_id, department_id, mark) values ('3', '1', '530');
解题思路
Students表保存了所有的学生信息。包括学生ID、部门ID、分数。
题目要求:计算每个学生在其部门内的排名。
部门内的排名是根据一个计算公式得出的:(student_rank_in_the_department – 1) * 100 / (the_number_of_students_in_the_department – 1)。
其中,student_rank_in_the_department表示学生分数在部门内的排名。如果分数相同,则排名相同。很明显,可以使用开窗排序的方法实现。
the_number_of_students_in_the_department表示部门内的学生数量。这个简单,直接根据部门GROUP BY汇总即可。
分别计算出每个学生的以上两个值后,代入公式计算即可。
不过,需要注意的是,如果某个部门仅有一名学生,公式中的分母为0,计算失败。此时题目要求,直接返回0的结果。
参考SQL
未特别说明的情况下,参考SQL为基于MySQL8.0实现。
with tmp1 as ( select student_id, rank() over(partition by department_id order by mark desc) rk from Students ), tmp2 as ( select department_id, count(1) cnt from Students group by department_id ) select a.student_id, a.department_id, case when c.cnt = 1 then 0.00 else round((b.rk-1)*100/(c.cnt-1),2) end percentage from Students a inner join tmp1 b on a.student_id = b.student_id inner join tmp2 c on a.department_id = c.department_id;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。