Mysql melaksanakan cara menulis sql untuk mendapatkan beberapa teratas dalam setiap kumpulan selepas membezakan mengikut kumpulan

Menghadapi senario di mana saya perlu mengumpulkan data dan kemudian mendapatkan 10 data pertama dalam setiap kumpulan Mula-mula saya terfikir untuk menggunakan kumpulan mengikut, tetapi kesukarannya bagaimana untuk mengetahui data selepas kumpulan Data tersebut berada dalam kumpulan.

1. Cipta jadual dan masukkan data ujian yang berkaitan

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;

Nota: Data sql yang dimasukkan berada di penghujung, rakan boleh mengesahkan mengikuti dengan sendirinya sql

2. Tanya tiga rekod teratas setiap subjek

Sekarang setelah data tersedia, kemudian tulis sql, sql adalah seperti berikut:

###每科成绩前三名
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

Analisis: Subkueri digunakan dalam

, dan sql teras ialah keadaan selepas di mana:

( SELECT count( * ) FROM score s2 WHERE s1.subject = s2.subject AND s1.score < s2.score ) < 3

Maksud ini sql ialah. . .

Saya rasa bahasa saya agak sukar untuk diterangkan, jadi saya akan menggunakan kod Java yang saya kenali untuk menerangkan SQL di atas Ia mungkin gelung for yang merentasi dua kali, dan Subjek yang sama dikira dalam gelung kedua untuk rekod pelajar, bilangan pelajar yang mendapat markah lebih tinggi daripada s1 Jika nombor ini kurang daripada 3, bermakna s1 berada di kedudukan tiga teratas >

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;
    }
}

Anda boleh melihat hasil cetakan dan pelaksanaan selepas kod dijalankan adalah sama

3 markah dalam setiap subjek adalah lebih besar daripada atau sama dengan 90 mata

Jadual dan data tersedia, dengan cara ini Ringkaskan beberapa soalan SQL jenis ini

Jika soalannya adalah untuk menanyakan rekod dalam jadual di atas dengan skor lebih besar daripada atau sama dengan 90 mata dalam setiap subjek, bagaimana untuk menulis SQL?

1. Cara pertama untuk menulis: Berfikiran positif

Jika markah dalam setiap mata pelajaran adalah lebih daripada 90 mata, maka markah terendah juga mestilah lebih besar daripada atau sama dengan 90 mata sql adalah seperti berikut:

SELECT
	* 
FROM
	score 
WHERE
	student_id IN  
	  (SELECT student_id FROM score GROUP BY student_id HAVING min( score ) >= 90 )

2 Cara penulisan kedua: Pemikiran songsang

Kecualikan rekod dengan markah tertinggi kurang daripada 90 mata

SELECT
	* 
FROM
	score 
WHERE
	student_id NOT IN  
	  (SELECT student_id FROM score GROUP BY student_id HAVING max( score ) < 90 )

Nota: Pemilihan ke hadapan dan terbalik bergantung pada situasi tertentu

Penceritaan lain

Soal rekod pelajar yang skor puratanya dalam setiap mata pelajaran melebihi 80 mata

###查询学生各科平均分大于80分的记录
select * from score where student_id in(
     select student_id from score GROUP BY student_id HAVING avg(score)>80
)

Soal rekod pelajar yang gagal dalam setiap mata pelajaran

###查询一个学生每科分数不及格的记录
SELECT
	* 
FROM
	score 
WHERE
	student_id IN 
	( SELECT student_id FROM score GROUP BY student_id HAVING max( score ) < 60 )

Lampiran: sql untuk sisipan struktur jadual

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);

Atas ialah kandungan terperinci Mysql melaksanakan cara menulis sql untuk mendapatkan beberapa teratas dalam setiap kumpulan selepas membezakan mengikut kumpulan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!