MySQL分组取前 N 条记录

score表:

CREATE TABLE `score` (
  `student_id` int(10) DEFAULT NULL,
  `class_id` int(10) DEFAULT NULL,
  `score` int(5) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

字段 student_id 学生 id ,class_id:班级 id ,score:分数

数据准备:

insert into score values(1,1,100),(2,1,93),(3,1,89),(4,1,96),(5,2,98),(6,2,97),(7,2,90),(8,2,88),(9,1,96);

表结构如下:

mysql> select * from score;+------------+----------+-------+| student_id | class_id | score |+------------+----------+-------+|          1 |        1 |   100 ||          2 |        1 |    93 ||          3 |        1 |    89 ||          4 |        1 |    96 ||          5 |        2 |    98 ||          6 |        2 |    97 ||          7 |        2 |    90 ||          8 |        2 |    88 ||          9 |        1 |    96 |+------------+----------+-------+9 rows in set (0.00 sec)

取每个班级前两名的学生(包含并列第二名)

mysql> select * from score s1 where (select count(0) from score s2 where s1.class_id = s2.class_id and s1.score < s2.score) < 2;+------------+----------+-------+| student_id | class_id | score |+------------+----------+-------+|          1 |        1 |   100 ||          4 |        1 |    96 ||          5 |        2 |    98 ||          6 |        2 |    97 ||          9 |        1 |    96 |+------------+----------+-------+5 rows in set (0.00 sec)

sql 解释:取表 s1的数据,这些数据中 class_id 和 s2 class_id相同的数据下,比 s1的 score 分数大的 s2的数据条目必须小于2或者使用 left join 的方式:

mysql> select s1.* from score s1 left join score s2 on s1.class_id = s2.class_id and s1.score<s2.score group by s1.class_id,s1.student_id,s1.score having count(s2.student_id)<2;+------------+----------+-------+| student_id | class_id | score |+------------+----------+-------+|          1 |        1 |   100 ||          4 |        1 |    96 ||          9 |        1 |    96 ||          5 |        2 |    98 ||          6 |        2 |    97 |+------------+----------+-------+5 rows in set (0.00 sec)

取学生分数数据且表示排名

mysql> select s1.*,(select count(0) + 1 from score s2 where s2.score > s1.score)rank from score s1;+------------+----------+-------+------+| student_id | class_id | score | rank |+------------+----------+-------+------+|          1 |        1 |   100 |    1 ||          2 |        1 |    93 |    6 ||          3 |        1 |    89 |    8 ||          4 |        1 |    96 |    4 ||          5 |        2 |    98 |    2 ||          6 |        2 |    97 |    3 ||          7 |        2 |    90 |    7 ||          8 |        2 |    88 |    9 ||          9 |        1 |    96 |    4 |+------------+----------+-------+------+9 rows in set (0.00 sec)