mysql中top问题(取前X名数据)

transitory_truth

已于 2024-07-30 11:15:34 修改

阅读量393

点赞数 18

文章标签： mysql 数据库

于 2024-07-30 11:11:05 首次发布

本文链接：https://blog.csdn.net/transitory_truth/article/details/140791856

版权

mysql中top问题(取前X名)

前置数据

建表语句

CREATE TABLE `student_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `no` int DEFAULT NULL COMMENT '学号',
  `subject_name` varchar(255) DEFAULT NULL COMMENT '科目名称',
  `score` int DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`id`),
  KEY `idx_subject` (`subject_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

创建数据语句

INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (1, 1, 'math', 91);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (2, 1, 'chinese', 90);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (3, 1, 'english', 87);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (4, 1, 'physics', 86);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (5, 1, 'chemistry', 88);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (6, 2, 'math', 85);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (7, 2, 'chinese', 84);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (8, 2, 'english', 83);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (9, 2, 'physics', 91);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (10, 2, 'chemistry', 91);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (11, 3, 'math', 76);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (12, 3, 'chinese', 45);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (13, 3, 'english', 32);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (14, 3, 'physics', 12);
INSERT INTO `student_info` (`id`, `no`, `subject_name`, `score`) VALUES (15, 3, 'chemistry', 40);

目的

获取每个学生最高的三门成绩

MySQL版本在8及以上

使用窗口函数

RANK()

执行SQL

SELECT 
* 
FROM 
(SELECT `no`,subject_name,score,RANK() over(partition by `no` ORDER BY score desc) `rank_num` FROM student_info)t 
WHERE t.rank_num <=3 
ORDER BY `no`,rank_num

执行结果

no	subject_name	score	rank_num
1	math	91	1
1	chinese	90	2
1	chemistry	88	3
2	physics	91	1
2	chemistry	91	1
2	math	85	3
3	math	76	1
3	chinese	45	2
3	chemistry	40	3

DENSE_RANK()

执行SQL

SELECT 
* 
FROM (SELECT `no`,subject_name,score,DENSE_RANK() over(partition by `no` ORDER BY score desc) `rank_num` FROM student_info)t 
WHERE t.rank_num <=3
 ORDER BY `no`,rank_num

执行结果

no	subject_name	score	rank_num
1	math	91	1
1	chinese	90	2
1	chemistry	88	3
2	physics	91	1
2	chemistry	91	1
2	math	85	2
2	chinese	84	3
3	math	76	1
3	chinese	45	2
3	chemistry	40	3

ROW_NUMBER()

执行SQL

SELECT 
* 
* FROM (SELECT `no`,subject_name,score,ROW_NUMBER() over(partition by `no` ORDER BY score desc) `rank_num` FROM student_info)t 
* WHERE t.rank_num <=3 
* ORDER BY `no`,rank_num

执行结果

no	subject_name	score	rank_num
1	math	91	1
1	chinese	90	2
1	chemistry	88	3
2	physics	91	1
2	chemistry	91	2
2	math	85	3
3	math	76	1
3	chinese	45	2
3	chemistry	40	3

注意

RANK、DENSE_RANK计算排名，如果值相同，会并列排名，ROW_NUMBER只是按顺序加序号，跟实际值无关
RANK会得到它前面所有记录行数的名次，比如前面两个第一名，它就是第三名。DENSE_RANK只会计算它前面有效值的行数，比如前面只有两个100分，它的99分就是第二名。
所以RANK、DENSE_RANK都会有获取到过多数据的可能
ROW_NUMBER在第一个查询语句指定排序不生效,在窗口函数内指定多个字段排序会导致结果异常
ROW_NUMBER并列计算值的顺序是否有可能不一致未确认，在作者当前示例中多次执行未发现结果有变化，查看结果是根据主键ID升序。如果没有主键ID结果未知

MySQL版本在8以下

使用子查询

执行SQL

SELECT 
e1.`no`,e1.subject_name,e1.score,
(SELECT count(e2.score) FROM	student_info AS e2 	WHERE	e1.score < e2.score	AND e1.no = e2.no)+1 rank_num
FROM
	student_info AS e1
WHERE
(SELECT  count(e2.score) FROM	student_info AS e2  WHERE	e1.score < e2.score	AND e1.no = e2.no) < 3
ORDER BY `no`,score DESC

执行结果

no	subject_name	score	rank_num
1	math	91	1
1	chinese	90	2
1	chemistry	88	3
2	physics	91	1
2	chemistry	91	1
2	math	85	3
3	math	76	1
3	chinese	45	2
3	chemistry	40	3

注意

可以看到有并列名次，在特殊情况下还是会取到过多的数据，比如获取前一条

使用变量

执行SQL

SELECT  
`no`,subject_name,score,rank_num 
FROM
( SELECT s.subject_name,s.score,@rankn:= IF(@sn = `no`,@rankn+1,1) rank_num,(@sn := `no`) `no` 
	FROM 
	student_info s,(SELECT @rankn:=1,@sn := '')t
	ORDER BY s.`no`,s.score DESC )t2
WHERE t2.rank_num <= 3
ORDER BY t2.`no`,t2.rank_num