mysql排名前十的学生_mysql学生成绩排名,分组取前N条记录

转载  https://blog.csdn.net/jslcylcy/article/details/72627762

score表:

CREATE TABLE &＃96;score&＃96; (

&＃96;student_id&＃96; int(10) DEFAULT NULL,

&＃96;class_id&＃96; int(10) DEFAULT NULL,

&＃96;score&＃96; int(5) DEFAULT NULL

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8mb4 COLLATE&＃61;utf8mb4_unicode_ci

1

2

3

4

5

字段 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;

&＃43;------------&＃43;----------&＃43;-------&＃43;

| student_id | class_id | score |

&＃43;------------&＃43;----------&＃43;-------&＃43;

| 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 |

&＃43;------------&＃43;----------&＃43;-------&＃43;

9 rows in set (0.00 sec)

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

mysql> select * from score s1 where (select count(0) from score s2 where s1.class_id &＃61; s2.class_id and s1.score

&＃43;------------&＃43;----------&＃43;-------&＃43;

| student_id | class_id | score |

&＃43;------------&＃43;----------&＃43;-------&＃43;

| 1 | 1 | 100 |

| 4 | 1 | 96 |

| 5 | 2 | 98 |

| 6 | 2 | 97 |

| 9 | 1 | 96 |

&＃43;------------&＃43;----------&＃43;-------&＃43;

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 &＃61; s2.class_id and s1.score

&＃43;------------&＃43;----------&＃43;-------&＃43;

| student_id | class_id | score |

&＃43;------------&＃43;----------&＃43;-------&＃43;

| 1 | 1 | 100 |

| 4 | 1 | 96 |

| 9 | 1 | 96 |

| 5 | 2 | 98 |

| 6 | 2 | 97 |

&＃43;------------&＃43;----------&＃43;-------&＃43;

5 rows in set (0.00 sec)

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

mysql> select s1.*,(select count(0) &＃43; 1 from score s2 where s2.score > s1.score)rank from score s1;

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

| student_id | class_id | score | rank |

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

| 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 |

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

9 rows in set (0.00 sec)

sql解释:将 s2中比s1中分数大的条目显示出来就行了(count 时需要加1)

3.取学生成绩数据,表示班级排名

mysql> select s1.*,(select count(0) &＃43; 1 from score s2 where s1.class_id &＃61; s2.class_id and s2.score > s1.score)rank from score s1 order by class_id,rank;

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

| student_id | class_id | score | rank |

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

| 1 | 1 | 100 | 1 |

| 4 | 1 | 96 | 2 |

| 9 | 1 | 96 | 2 |

| 2 | 1 | 93 | 4 |

| 3 | 1 | 89 | 5 |

| 5 | 2 | 98 | 1 |

| 6 | 2 | 97 | 2 |

| 7 | 2 | 90 | 3 |

| 8 | 2 | 88 | 4 |

&＃43;------------&＃43;----------&＃43;-------&＃43;------&＃43;

9 rows in set (0.00 sec)

与之前一样,但过滤条件中只需要计算班级相同的数据条目

4.取每个班级前两名(并列的只取前面的数据)