mysql實現(xiàn)按組區(qū)分后獲取每組前幾名的sql寫法
更新時間：2023年03月20日 09:09:29 作者：新林。
這篇文章主要介紹了mysql實現(xiàn)按組區(qū)分后獲取每組前幾名的sql寫法，具有很好的參考價值，希望對大家有所幫助。
遇到一個場景，要把數(shù)據分組，然后獲取每組前10條數(shù)據，首先我想到用group by分組，但是難點是分組后怎么知道該數(shù)據在組里面排第幾條。
研究了一下，寫個demo,記錄一下筆記,文章后面順便也記錄一下常規(guī)sql的一些考試題（統(tǒng)計各科學生成績大于多少多少分的題目）
一、創(chuàng)建表，插入相關測試數(shù)據
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;
備注：插入的數(shù)據sql在最后面，小伙伴可以自行驗證下面的sql
二、查詢每科成績前三的記錄
數(shù)據有了，那么寫sql,sql如下：
###每科成績前三名 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后面的這個條件：
( SELECT count( * ) FROM score s2 WHERE s1.subject = s2.subject AND s1.score < s2.score ) < 3
這段sql的意思是。。。
感覺我的語言有點描述不出來，還是用我熟悉的java代碼描述上面的sql,大概就是for循環(huán)遍歷兩次，在第二次for循環(huán)的時候統(tǒng)計同一科目的學生記錄，比s1的學生分數(shù)高的數(shù)量，如果這個數(shù)量小于3的話，說明s1排名前三，看下面的代碼理解理解
public class StudentTest {
public static void main(String[] args) {
List<Student> list = new ArrayList<>();
//初始化和表結構一致的數(shù)據
initData(list);
//記錄查詢出來的結果
List<Student> result = new ArrayList<>();
for(Student s1 : list){
int num = 0;
//兩次for循環(huán)遍歷，相當于sql里面的子查詢
for(Student s2:list){
//統(tǒng)計同一科目，且分數(shù)s2分數(shù)大于s1的數(shù)量，簡單理解就是同一科目的學生記錄，比s1的學生分數(shù)高的數(shù)量
if(s1.getSubject().equals(s2.getSubject())
&&s1.getScore()<s2.getScore()){
num++;
}
}
//比s1的學生分數(shù)高的數(shù)量,如果小于3的話，說明s1這個排名前三
// 舉例：num=0時，說明同一科目，沒有一個學生成績高于s1學生， s1學生的這科成績排名第一
// num =1,時，s1學生排名第二，num=3時：說明排名同一科目有三個學生成績高過s1，s1排第四，所以只統(tǒng)計前三的學生，條件就是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,"數(shù)學","張三",78));
list.add(new Student(3,"英語","張三",65));
list.add(new Student(4,"語文","李四",88));
list.add(new Student(5,"數(shù)學","李四",58));
list.add(new Student(6,"英語","李四",65));
list.add(new Student(7,"語文","王五",92));
list.add(new Student(8,"數(shù)學","王五",99));
list.add(new Student(9,"英語","王五",96));
list.add(new Student(10,"語文","小張",90));
list.add(new Student(11,"數(shù)學","小張",91));
list.add(new Student(12,"英語","小張",90));
list.add(new Student(13,"語文","小華",88));
list.add(new Student(14,"數(shù)學","小華",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;
}
}
可以看到代碼運行完打印出來的結果和執(zhí)行sql后的結果是一樣的
三、查詢學生各科分數(shù)大于等于90分的記錄
表和數(shù)據都有了，順便也總結一些這類型的sql題
如題目為查詢上面表的各科成績都大于等于90分的記錄，那么sql怎么寫？
1. 第一種寫法：正向思考
各科成績都大于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 )
備注：正向和逆向看具體情況選擇
其他的插敘
查詢學生各科平均分大于80分的記錄
###查詢學生各科平均分大于80分的記錄
select * from score where student_id in(
select student_id from score GROUP BY student_id HAVING avg(score)>80
)
查詢一個學生每科分數(shù)不及格的記錄
###查詢一個學生每科分數(shù)不及格的記錄 SELECT * FROM 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, '數(shù)學', 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ù)學', 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ù)學', 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ù)學', 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ù)學', 5, '小華', 79); INSERT INTO `test`.`score`(`id`, `subject`, `student_id`, `student_name`, `score`) VALUES (15, '英語', 5, '小華', 77);
總結
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
為了日后安裝數(shù)據庫方便,下面這篇文章主要給大家介紹了關于MySQL自動安裝批處理腳本的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09
這篇文章主要介紹了JSP連接MySQL數(shù)據庫詳細步驟，文章內容詳細全面，且通過實例進行講解，容易理解，需要的朋友可以參考下2023-01-01
這篇文章主要介紹了MySQL修改時區(qū)的方法,總結分析了三種常見的MySQL時區(qū)修改技巧,包括命令行模式、配置文件方式及代碼方式,需要的朋友可以參考下2016-05-05
當需要停止或關閉MySQL實例時,很多人并不清楚如何做到正確、安全地關閉MySQL,會對數(shù)據的安全性和穩(wěn)定性造成很大的威脅,本文將從多個方面詳細闡述如何正確關閉MySQL,包括關閉MySQL的基本方法、關閉MySQL前的準備工作、優(yōu)雅地關閉MySQL以及遇到MySQL無法正常關閉的情況2023-09-09
MySQL數(shù)據庫存入Date時天數(shù)會少一天的問題及解決
這篇文章主要介紹了MySQL數(shù)據庫存入Date時天數(shù)會少一天的問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
對于客戶端驗證用戶輸入的身份證是否符合格式的代碼，需要的朋友可以參考下。2010-11-11
Ubuntu10下如何搭建MySQL Proxy讀寫分離探討
MySQL Proxy是一個處于你的Client端和MySQL server端之間的簡單程序，它可以監(jiān)測、分析或改變它們的通信2012-11-11
Windows MySQL修改配置文件my.ini不生效問題
在Windows Server 2019上修改MySQL 5.6的安裝目錄下my.ini文件后,需要通過修改注冊表中的ImagePath值來確保MySQL讀取新的配置文件,修改時應確保配置文件路徑正確,并且新配置不會覆蓋原有配置,以保證修改生效2025-01-01
解決 phpmyadmin #2002 無法登錄 MySQL 服務器
我以前使用phpmyadmin都是很正常的，從來沒有出現(xiàn)過問題。但是今天出現(xiàn)了提示#2002無法登陸到MYSQL服務器2012-04-04
這篇文章主要介紹了MySQL實現(xiàn)分布式鎖，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08