您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“mysql實(shí)現(xiàn)按組區(qū)分后獲取每組前幾名的sql怎么寫”的有關(guān)知識,在實(shí)際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
遇到一個場景,要把數(shù)據(jù)分組,然后獲取每組前10條數(shù)據(jù),首先我想到用group by分組,但是難點(diǎn)是分組后怎么知道該數(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 '成績', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
備注:插入的數(shù)據(jù)sql在最后面,小伙伴可以自行驗(yàn)證下面的sql
數(shù)據(jù)有了,那么寫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的意思是。。。
感覺我的語言有點(diǎn)描述不出來,還是用我熟悉的java代碼描述上面的sql,大概就是for循環(huán)遍歷兩次,在第二次for循環(huán)的時候統(tǒng)計(jì)同一科目的學(xué)生記錄,比s1的學(xué)生分?jǐn)?shù)高的數(shù)量,如果這個數(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ù)量,簡單理解就是同一科目的學(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這個排名前三 // 舉例:num=0時,說明同一科目,沒有一個學(xué)生成績高于s1學(xué)生, s1學(xué)生的這科成績排名第一 // num =1,時,s1學(xué)生排名第二,num=3時:說明排名同一科目有三個學(xué)生成績高過s1,s1排第四,所以只統(tǒng)計(jì)前三的學(xué)生,條件就是num<3 if(num < 3){ result.add(s1); } } //輸出各科成績前三的記錄 result.stream() .sorted(Comparator.comparing(Student::getSubject)) .forEach( s-> System.out.println(String.format("學(xué)生:%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ù)學(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é)果是一樣的
表和數(shù)據(jù)都有了,順便也總結(jié)一些這類型的sql題
如題目為查詢上面表的各科成績都大于等于90分的記錄,那么sql怎么寫?
各科成績都大于90分的,那么最低分的也必須大于等于90分,sql如下:
SELECT * FROM score WHERE student_id IN (SELECT student_id FROM score GROUP BY student_id HAVING min( score ) >= 90 )
排除最高分都小于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 )
查詢一個學(xué)生每科分?jǐn)?shù)不及格的記錄
###查詢一個學(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 '成績', 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);
“mysql實(shí)現(xiàn)按組區(qū)分后獲取每組前幾名的sql怎么寫”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。