mysql组内取前的几种写法

题目：按照性别分组，各取组内分数前三名同学

测试数据脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sex` int(11) NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 'geng1', 1, 100);
INSERT INTO `student` VALUES (2, 'geng2', 1, 96);
INSERT INTO `student` VALUES (3, 'geng3', 1, 97);
INSERT INTO `student` VALUES (4, 'geng4', 1, 97);
INSERT INTO `student` VALUES (5, 'geng5', 0, 23);
INSERT INTO `student` VALUES (6, 'geng6', 1, 95);
INSERT INTO `student` VALUES (7, 'geng7', 0, 0);

SET FOREIGN_KEY_CHECKS = 1;

1. 场景一 （不管是否有并列排名只取分数最高的前三名同学）

image

1.1 方法1 利用分组和聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	s1.* 
FROM
	student s1
	LEFT JOIN student s2 ON s1.sex = s2.sex  
	AND s2.score > s1.score 
GROUP BY
	s1.id 
HAVING
	count( s1.id ) < 3 
ORDER BY
	s1.id

1.2 自定义变量排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
	s.id, 
	s.name,
	s.sex,
	s.score
FROM
	(
SELECT
	student.*,
IF( @p = sex, @r := @r + 1, @r := 1 ) AS rank2 , @p:=sex
FROM
	student,
	( SELECT @p := NULL, @r := 0 ) r 
ORDER BY
	sex,
	score desc 
	) s where s.rank2 <= 3

1.3 mysql8.0 利用 rank() over() 函数

1
2
3
4
5
select  id,name,sex,score from (
SELECT student.* ,
rank() over(partition by sex  order by score desc ) as rank3 from student  
) s
where s.rank3 <= 3

注意： 在这种不考虑并列排名的情况下如果只是输出结果的人数固定，则需要考虑在并列排名的情况下，输出的附加条件。比如一共取三个人，但是并列第二名的就有四个，如何输出，需要附加条件。

2.场景二 （并列排名不影响后续排名名次，按照名次输出，不考虑输出人数）

image

2.1 自定义变量排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT id,name,sex,score
FROM
(
SELECT *,
IF(@p=sex,
    CASE 
       WHEN @s=score THEN @r
       else @r:=@r+1
    END,
   @r:=1 ) AS rank2,
@p:=sex,
@s:=score
FROM student,(SELECT @p:=NULL,@s:=NULL,@r:=0) r
ORDER BY sex,score  desc
)s   where s.rank2 < 4

2.2 MySQL8.0 利用dense_rank() over ()

1
2
3
4
5
select  id,name,sex,score from (
SELECT student.* ,
dense_rank() over(partition by sex  order by score desc ) as rank3 from student  
) s
where s.rank3 <= 3

总结：组内取前的写法应该还有很多方式，欢迎各位大佬补充，指正。
遇到如下报错的时候也可以考虑上面的写法：
This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ mysql

在leetcode 题库内也有类似的题目《部门工资前三高的所有员工》