Maison > Article > base de données > Mysql implémente comment écrire SQL pour obtenir les premiers de chaque groupe après avoir distingué par groupe
Mysql implémente comment écrire SQL pour obtenir les premiers de chaque groupe après avoir distingué par groupe
- 王林avant
- 2023-05-31 09:01:241677parcourir
J'ai rencontré un scénario dans lequel je devais regrouper les données, puis obtenir les 10 premières données de chaque groupe. J'ai d'abord pensé à utiliser le groupe par, mais la difficulté était de savoir quelle donnée était classée. dans le groupe après le regroupement.
1. Créez un tableau et insérez les données de test pertinentes
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;
Remarque : les données SQL insérées sont à la fin, les amis peuvent vérifier eux-mêmes le SQL suivant
2. scores dans chaque sujet
Maintenant que les données sont disponibles, écrivez le SQL comme suit :
###每科成绩前三名 SELECT * FROM score s1 WHERE ( SELECT count( * ) FROM score s2 WHERE s1.`subject` = s2.`subject` AND s1.score < s2.score ) < 3 ORDER BY SUBJECT, score DESC
Analyse :
utilise une sous-requête, et le noyau SQL est la condition après où :
( SELECT count( * ) FROM score s2 WHERE s1.subject = s2.subject AND s1.score < s2.score ) < 3
La signification de ce SQL est. . .
Je pense que mon langage est un peu difficile à décrire, je vais donc utiliser le code java que je connais pour décrire le sql ci-dessus. Il s'agit probablement d'une boucle for parcourue deux fois, et dans la seconde boucle for, l'étudiant. les enregistrements de la même matière sont comptés, ce qui est meilleur que celui de s1. Le nombre d'élèves avec des scores élevés si ce nombre est inférieur à 3, cela signifie que s1 se classe parmi les trois premiers. Regardez le code ci-dessous pour le comprendre.
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;
}
}Vous pouvez voir que le résultat imprimé après l'exécution du code est le même que le résultat après l'exécution du sql
3 Interrogez les dossiers des étudiants dont les scores dans chaque matière sont supérieurs ou égaux à 90 points
.Le tableau et les données sont tous là. D'ailleurs, nous résumons également quelques questions SQL de ce type
Par exemple, le titre est d'interroger le tableau ci-dessus pour tous les sujets dont les scores sont supérieurs ou égaux à 90 points d'enregistrement. , comment écrire du SQL ?
1. La première façon d'écrire : la réflexion prospective
Si les scores dans chaque matière sont supérieurs à 90 points, alors le score le plus bas doit également être supérieur ou égal à 90 points. Le sql est le suivant :
SELECT * FROM score WHERE student_id IN (SELECT student_id FROM score GROUP BY student_id HAVING min( score ) >= 90 )
. 2. La deuxième façon d'écrire : penser à rebours
Exclure les enregistrements dont le score le plus élevé est inférieur à 90 points
SELECT * FROM score WHERE student_id NOT IN (SELECT student_id FROM score GROUP BY student_id HAVING max( score ) < 90 )
Remarque : la sélection avant et arrière dépend de la situation spécifique
Autres récits
Vérifiez les enregistrements des étudiants dont le score moyen dans chaque matière est supérieur à 80 points
###查询学生各科平均分大于80分的记录
select * from score where student_id in(
select student_id from score GROUP BY student_id HAVING avg(score)>80
)Requête 1 Enregistrements des scores d'échec des étudiants dans chaque matière
###查询一个学生每科分数不及格的记录 SELECT * FROM score WHERE student_id IN ( SELECT student_id FROM score GROUP BY student_id HAVING max( score ) < 60 )
Pièce jointe : SQL inséré dans la structure de la table
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);
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!