mysql實(shí)現(xiàn)按組區(qū)分后獲取每組前幾名的sql寫法

更新時(shí)間：2023年03月20日 09:09:29 作者：新林。

這篇文章主要介紹了mysql實(shí)現(xiàn)按組區(qū)分后獲取每組前幾名的sql寫法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。

一、創(chuàng)建表，插入相關(guān)測(cè)試數(shù)據(jù)

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `subject` varchar(20) DEFAULT NULL COMMENT '科目',
  `student_id` int(11) DEFAULT NULL COMMENT '學(xué)生id',
  `student_name` varchar(20) NOT NULL COMMENT '學(xué)生姓名',
  `score` double DEFAULT NULL COMMENT '成績(jī)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

備注：插入的數(shù)據(jù)sql在最后面，小伙伴可以自行驗(yàn)證下面的sql

二、查詢每科成績(jī)前三的記錄

數(shù)據(jù)有了，那么寫sql,sql如下：

###每科成績(jī)前三名
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

分析：

里面用了子查詢，核心sql是where后面的這個(gè)條件：

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

這段sql的意思是。。。

感覺我的語言有點(diǎn)描述不出來，還是用我熟悉的java代碼描述上面的sql,大概就是for循環(huán)遍歷兩次，在第二次for循環(huán)的時(shí)候統(tǒng)計(jì)同一科目的學(xué)生記錄，比s1的學(xué)生分?jǐn)?shù)高的數(shù)量，如果這個(gè)數(shù)量小于3的話，說明s1排名前三，看下面的代碼理解理解

public class StudentTest {
   
    public static void main(String[] args) {
        List<Student> list = new ArrayList<>();
        //初始化和表結(jié)構(gòu)一致的數(shù)據(jù)
        initData(list);
        //記錄查詢出來的結(jié)果
        List<Student> result = new ArrayList<>();
        for(Student s1 : list){
            int num = 0;
            //兩次for循環(huán)遍歷，相當(dāng)于sql里面的子查詢
            for(Student s2:list){
                //統(tǒng)計(jì)同一科目，且分?jǐn)?shù)s2分?jǐn)?shù)大于s1的數(shù)量，簡(jiǎn)單理解就是同一科目的學(xué)生記錄，比s1的學(xué)生分?jǐn)?shù)高的數(shù)量
                if(s1.getSubject().equals(s2.getSubject())
                        &&s1.getScore()<s2.getScore()){
                    num++;
                }
            }
            //比s1的學(xué)生分?jǐn)?shù)高的數(shù)量,如果小于3的話，說明s1這個(gè)排名前三
            // 舉例：num=0時(shí)，說明同一科目，沒有一個(gè)學(xué)生成績(jī)高于s1學(xué)生， s1學(xué)生的這科成績(jī)排名第一
            // num =1,時(shí)，s1學(xué)生排名第二，num=3時(shí)：說明排名同一科目有三個(gè)學(xué)生成績(jī)高過s1，s1排第四，所以只統(tǒng)計(jì)前三的學(xué)生，條件就是num<3
            if(num < 3){
                result.add(s1);
            }
        }
        //輸出各科成績(jī)前三的記錄
        result.stream()
                .sorted(Comparator.comparing(Student::getSubject))
                .forEach(
             s-> System.out.println(String.format("學(xué)生:%s,科目：%s,成績(jī):%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,"數(shù)學(xué)","張三",78));
           list.add(new Student(3,"英語","張三",65));
           list.add(new Student(4,"語文","李四",88));
           list.add(new Student(5,"數(shù)學(xué)","李四",58));
           list.add(new Student(6,"英語","李四",65));
           list.add(new Student(7,"語文","王五",92));
           list.add(new Student(8,"數(shù)學(xué)","王五",99));
           list.add(new Student(9,"英語","王五",96));
           list.add(new Student(10,"語文","小張",90));
           list.add(new Student(11,"數(shù)學(xué)","小張",91));
           list.add(new Student(12,"英語","小張",90));
           list.add(new Student(13,"語文","小華",88));
           list.add(new Student(14,"數(shù)學(xué)","小華",79));
           list.add(new Student(15,"英語","小華",77));
    }
    
    @Data
    public static class Student {
    private int id;
    private String subject;
    private String name;
    private double score;
    //想當(dāng)于表結(jié)構(gòu)
    public Student(int id, String subject, String name, double score) {
        this.id = id;
        this.subject = subject;
        this.name = name;
        this.score = score;
    }
}

可以看到代碼運(yùn)行完打印出來的結(jié)果和執(zhí)行sql后的結(jié)果是一樣的

三、查詢學(xué)生各科分?jǐn)?shù)大于等于90分的記錄

表和數(shù)據(jù)都有了，順便也總結(jié)一些這類型的sql題

如題目為查詢上面表的各科成績(jī)都大于等于90分的記錄，那么sql怎么寫？

1. 第一種寫法：正向思考

各科成績(jī)都大于90分的，那么最低分的也必須大于等于90分，sql如下：

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

2. 第二種寫法：逆向思考

排除最高分都小于90分的記錄

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

備注：正向和逆向看具體情況選擇

其他的插敘

查詢學(xué)生各科平均分大于80分的記錄

###查詢學(xué)生各科平均分大于80分的記錄
select * from score where student_id in(
     select student_id from score GROUP BY student_id HAVING avg(score)>80
)

查詢一個(gè)學(xué)生每科分?jǐn)?shù)不及格的記錄

###查詢一個(gè)學(xué)生每科分?jǐn)?shù)不及格的記錄
SELECT
	* 
FROM
	score 
WHERE
	student_id IN 
	( SELECT student_id FROM score GROUP BY student_id HAVING max( score ) < 60 )

附：表結(jié)構(gòu)插入的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 '學(xué)生id',
  `student_name` varchar(20) NOT NULL COMMENT '學(xué)生姓名',
  `score` double DEFAULT NULL COMMENT '成績(jī)',
  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, '數(shù)學(xué)', 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, '數(shù)學(xué)', 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, '數(shù)學(xué)', 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, '數(shù)學(xué)', 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, '數(shù)學(xué)', 5, '小華', 79);
INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (15, '英語', 5, '小華', 77);