Mysql实现分组查询每个班级的前三名
2020-07-16 06:14
阅读数 160
1.先创建表
CREATE TABLE IF NOT EXISTS student(
id varchar(20),-- 编号
class varchar(20),-- 年级
score int-- 分数
);
2.插入相关数据
INSERT INTO student VALUES ('1','一班',88);
INSERT INTO student VALUES ('2','一班',100);
INSERT INTO student VALUES ('3','一班',65);
INSERT INTO student VALUES ('4','一班',78);
INSERT INTO student VALUES ('5','一班',36);
INSERT INTO student VALUES ('6','二班',88);
INSERT INTO student VALUES ('7','二班',82);
INSERT INTO student VALUES ('8','二班',96);
INSERT INTO student VALUES ('9','二班',53);
INSERT INTO student VALUES ('10','二班',72);
INSERT INTO student VALUES ('11','三班',88);
INSERT INTO student VALUES ('12','三班',66);
INSERT INTO student VALUES ('13','三班',89);
INSERT INTO student VALUES ('14','三班',99);
INSERT INTO student VALUES ('15','三班',56);
INSERT INTO student VALUES ('16','三班',72);
3.查询
SELECT
a.class,
a.score
FROM
student a
WHERE
(
SELECT
count(*)
FROM
student
WHERE
a.class = class
AND a.score < score
) < 3
ORDER BY
a.class,
a.score DESC;
结果
分析： 查询出每个班级的前三名就是 每个人统计处自己班级里超过自己分数的有几个，然后筛选出小于三个的
where中的select是保证：遍历所有记录，取每条记录与当前记录做比较，只有当student表中同一班级不超过3个人分数
比自己高时，这个学生才算是分数排行的前三名。也就是括号里面是查询在这一班级中分数比他高的人的数量。