共计 5427 个字符，预计需要花费 14 分钟才能阅读完成。
本篇内容介绍了“mysql 实现按组区分后获取每组前几名的 sql 怎么写”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
遇到一个场景，要把数据分组，然后获取每组前 10 条数据，首先我想到用 group by 分组，但是难点是分组后怎么知道该数据在组里面排第几条。
一、创建表，插入相关测试数据
CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键 ,
`subject` varchar(20) DEFAULT NULL COMMENT 科目 ,
`student_id` int(11) DEFAULT NULL COMMENT 学生 id ,
`student_name` varchar(20) NOT NULL COMMENT 学生姓名 ,
`score` double DEFAULT NULL COMMENT 成绩 ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;备注：插入的数据 sql 在最后面，小伙伴可以自行验证下面的 sql
二、查询每科成绩前三的记录
数据有了，那么写 sql,sql 如下：
### 每科成绩前三名
SELECT
*
score s1
WHERE
( SELECT count( * ) FROM score s2
WHERE s1.`subject` = s2.`subject` AND s1.score s2.score
) 3
ORDER BY
SUBJECT,
score DESC分析：
里面用了子查询，核心 sql 是 where 后面的这个条件：
( SELECT count( * ) FROM score s2 WHERE s1.subject = s2.subject AND s1.score s2.score ) 3这段 sql 的意思是。。。
感觉我的语言有点描述不出来，还是用我熟悉的 java 代码描述上面的 sql, 大概就是 for 循环遍历两次，在第二次 for 循环的时候统计同一科目的学生记录，比 s1 的学生分数高的数量，如果这个数量小于 3 的话，说明 s1 排名前三，看下面的代码理解理解
public class StudentTest {
public static void main(String[] args) { List Student list = new ArrayList ();
// 初始化和表结构一致的数据
initData(list);
// 记录查询出来的结果
List Student result = new ArrayList ();
for(Student s1 : list){
int num = 0;
// 两次 for 循环遍历，相当于 sql 里面的子查询
for(Student s2:list){
// 统计同一科目，且分数 s2 分数大于 s1 的数量，简单理解就是同一科目的学生记录，比 s1 的学生分数高的数量
if(s1.getSubject().equals(s2.getSubject())
s1.getScore() s2.getScore()){
num++;
}
}
// 比 s1 的学生分数高的数量, 如果小于 3 的话，说明 s1 这个排名前三
// 举例：num= 0 时，说明同一科目，没有一个学生成绩高于 s1 学生， s1 学生的这科成绩排名第一
// num =1, 时，s1 学生排名第二，num= 3 时：说明排名同一科目有三个学生成绩高过 s1，s1 排第四，所以只统计前三的学生，条件就是 num 3
if(num 3){ result.add(s1);
}
}
// 输出各科成绩前三的记录
result.stream()
.sorted(Comparator.comparing(Student::getSubject))
.forEach( s- System.out.println(String.format( 学生:%s, 科目：%s, 成绩:%s ,s.getName(),s.getSubject(),s.getScore()))
);
}
public static void initData(List Student list) {
list.add(new Student(1, 语文 , 张三 ,59));
list.add(new Student(2, 数学 , 张三 ,78));
list.add(new Student(3, 英语 , 张三 ,65));
list.add(new Student(4, 语文 , 李四 ,88));
list.add(new Student(5, 数学 , 李四 ,58));
list.add(new Student(6, 英语 , 李四 ,65));
list.add(new Student(7, 语文 , 王五 ,92));
list.add(new Student(8, 数学 , 王五 ,99));
list.add(new Student(9, 英语 , 王五 ,96));
list.add(new Student(10, 语文 , 小张 ,90));
list.add(new Student(11, 数学 , 小张 ,91));
list.add(new Student(12, 英语 , 小张 ,90));
list.add(new Student(13, 语文 , 小华 ,88));
list.add(new Student(14, 数学 , 小华 ,79));
list.add(new Student(15, 英语 , 小华 ,77));
}
@Data
public static class Student {
private int id;
private String subject;
private String name;
private double score;
// 想当于表结构
public Student(int id, String subject, String name, double score) {
this.id = id;
this.subject = subject;
this.name = name;
this.score = score;
}
}可以看到代码运行完打印出来的结果和执行 sql 后的结果是一样的
三、查询学生各科分数大于等于 90 分的记录
表和数据都有了，顺便也总结一些这类型的 sql 题
如题目为查询上面表的各科成绩都大于等于 90 分的记录，那么 sql 怎么写？
1. 第一种写法：正向思考
各科成绩都大于 90 分的，那么最低分的也必须大于等于 90 分，sql 如下：
SELECT
*
score
WHERE
student_id IN
(SELECT student_id FROM score GROUP BY student_id HAVING min( score ) = 90 )2. 第二种写法：逆向思考
排除最高分都小于 90 分的记录
SELECT
*
score
WHERE
student_id NOT IN
(SELECT student_id FROM score GROUP BY student_id HAVING max( score ) 90 )备注：正向和逆向看具体情况选择
其他的插叙
查询学生各科平均分大于 80 分的记录
### 查询学生各科平均分大于 80 分的记录
select * from score where student_id in( select student_id from score GROUP BY student_id HAVING avg(score) 80
)查询一个学生每科分数不及格的记录
### 查询一个学生每科分数不及格的记录
SELECT
*
score
WHERE
student_id IN
( SELECT student_id FROM score GROUP BY student_id HAVING max( score ) 60 )附：表结构插入的 sql
CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键 ,
`subject` varchar(20) DEFAULT NULL COMMENT 科目 ,
`student_id` int(11) DEFAULT NULL COMMENT 学生 id ,
`student_name` varchar(20) NOT NULL COMMENT 学生姓名 ,
`score` double DEFAULT NULL COMMENT 成绩 ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (1, 语文 , 1, 张三 , 59);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (2, 数学 , 1, 张三 , 78);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (3, 英语 , 1, 张三 , 65);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (4, 语文 , 2, 李四 , 88);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (5, 数学 , 2, 李四 , 58);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (6, 英语 , 2, 李四 , 65);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (7, 语文 , 3, 王五 , 92);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (8, 数学 , 3, 王五 , 99);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (9, 英语 , 3, 王五 , 96);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (10, 语文 , 4, 小张 , 90);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (11, 数学 , 4, 小张 , 91);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (12, 英语 , 4, 小张 , 90);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (13, 语文 , 5, 小华 , 88);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (14, 数学 , 5, 小华 , 79);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (15, 英语 , 5, 小华 , 77);“mysql 实现按组区分后获取每组前几名的 sql 怎么写”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
