MySQL分组Top N的问题

发表于 更新于 分类于 ， 阅读次数： Waline：

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

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

创建学生分数表

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