mysql实现分组查询每个班级的前三名、每门课程的前三名

1、 创建表

drop table student;
create table student(
	id varchar(20),-- 编号
	class varchar(20),-- 年级
	score int-- 分数
);

2、插入数据

delete from student;
insert student values('1','一年级',82);
insert student values('2','一年级',95);
insert student values('3','一年级',82);
insert student values('4','一年级',40);
insert student values('5','一年级',20);
insert student values('6','二年级',95);
insert student values('7','二年级',40);
insert student values('8','二年级',3);
insert student values('9','二年级',60);
insert student values('10','二年级',10);
insert student values('11','三年级',70);
insert student values('12','三年级',60);
insert student values('13','三年级',40);
insert student values('14','三年级',90);
insert student values('15','三年级',20);

3、第一种方法

如果多人并列第一或者第二，全部可以查出来

SELECT
    * 
FROM
    student a 
WHERE
    ( SELECT count(*) FROM student b WHERE b.class = a.class AND a.score < b.score )< 3 
ORDER BY
    a.class,
    a.score DESC;

注意如果查前2名，第二名的有两个，有并列

SELECT
    * 
FROM
    student a 
WHERE
    ( SELECT count(*) FROM student b WHERE b.class = a.class AND a.score < b.score )< 2 
ORDER BY
    a.class,
    a.score DESC;

4、第二种方法

SELECT
    a.*,
    COUNT( a.id ) 
FROM
    student a,
    student b 
WHERE
    b.class = a.class 
    AND a.score <= b.score 
GROUP BY
    a.id,
    a.class,
    a.score 
HAVING
    COUNT( a.id )<4 
ORDER BY
    a.class,
    a.score DESC;

如果查前两名则无法查询出分数为82 的，无法查询出并列的

SELECT
    a.*,
    COUNT( a.id ) 
FROM
    student a,
    student b 
WHERE
    b.class = a.class 
    AND a.score <= b.score 
GROUP BY
    a.id,
    a.class,
    a.score 
HAVING
    COUNT( a.id )<3
ORDER BY
    a.class,
    a.score DESC;

5、第三种方法

结果同第一种方法，不再演示结果

SELECT
    a.*,
    COUNT( a.id ) 
FROM
    student a
    LEFT JOIN student b ON b.class = a.class 
WHERE
    a.score >= b.score 
GROUP BY
    a.id,
    a.class,
    a.score 
HAVING
    COUNT( a.id )>3 
ORDER BY
    a.class,
    a.score DESC;

其中>3 这里,判断首先根据sql （去掉HAVING COUNT( a.id )>3 ）后，选择COUNT( a.id )>3 最大的三个数字，找到第四大的后，大于该数字即可

6、每门科目的前三名

其实类似该种方法，不在赘述