MySql 实现查询每个班级学习成绩前N的学生
首先建一张学生成绩信息表:
CREATE TABLE t_student (
stu_id int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
stu_name varchar(50) NOT NULL COMMENT '学生名称',
score decimal(5, 2) NOT NULL COMMENT '学生总成绩',
class_id int(11) NOT NULL COMMENT '班级ID',
PRIMARY KEY (stu_id),
INDEX IDX_t_student_score (score)
)
ENGINE = INNODB
AUTO_INCREMENT = 7
AVG_ROW_LENGTH = 2730
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = '学生信息表'
ROW_FORMAT = DYNAMIC;
插入如下的数据:
1 张三 80.00 1
2 李四 81.00 1
3 信达 70.00 1
4 鲁西 90.00 2
5 米亚 93.00 1
6 凯斯 85.00 2
查询每个班级学生成绩前三的学生信息,查询脚本如下:
SELECT a.stu_name,a.score,a.class_id FROM t_student a LEFT JOIN t_student b
ON a.class_id = b.class_id AND a.score < b.score
GROUP BY a.class_id ,a.stu_id,a.score
HAVING COUNT(b.stu_id) < 3 ;
查询结果: