0%

MySQL分组Top N的问题

经常会遇到分组求前几名的问题。比如,求每个班级中总分排名前三的学生。

我们通过如下实验看看怎么解决这个问题。

创建学生分数表

1
2
3
4
5
6
7
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
`class` varchar(255) DEFAULT NULL COMMENT '班级',
`score` int(11) DEFAULT NULL COMMENT '总分',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建若干条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER //
DROP PROCEDURE IF EXISTS batchInsert;
CREATE PROCEDURE batchInsert(n INT)
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < n DO
INSERT INTO score(name, class, score) VALUES
(substring(MD5(RAND()), 1, 10), CONCAT('G', ROUND(RAND() * 9)), ROUND(RAND() * 100));
SET i = i + 1;
END WHILE;
END
//
DELIMITER ;
CALL batchInsert(50);

TOP N

HAVING子句中的< 3表示取前两条。

1
2
3
4
5
SELECT s1.class, s1.score FROM score s1
LEFT JOIN score s2 ON s1.class = s2.class AND s1.score <= s2.score
GROUP BY s1.class, s1.score
HAVING COUNT(1) < 3
ORDER BY s1.class, s1.score DESC;

如果是取TOP 1,那就不能用这个自连接了,不然效率非常低下。还是得用MIN, MAX函数来解决。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
class   score
G0 66
G0 17
G1 96
G1 92
G2 87
G2 83
G3 67
G3 32
G4 94
G4 37
G5 99
G5 74
G6 40
G7 98
G7 78
G8 100
G8 93
G9 99
G9 92