mysql實現按組區分后獲取每組前幾名的sql怎么寫

177次閱讀

共計 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 小編將為大家輸出更多高質量的實用文章！

正文完