Mysql은 그룹별로 구분한 후 각 그룹의 상위 몇 개를 얻기 위해 sql을 작성하는 방법을 구현합니다.

데이터를 그룹화한 다음 각 그룹의 처음 10개 데이터를 가져와야 하는 시나리오에 직면했습니다. 처음에는 그룹별을 사용하려고 했지만 어떤 데이터가 순위가 매겨졌는지 아는 방법이 어려웠습니다. 그룹화 후 그룹에.

1. 테이블을 생성하고 관련 테스트 데이터를 삽입하세요

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은 친구들이 직접 확인할 수 있습니다

2. 각 과목별 점수

이제 데이터가 확보되었으므로 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 루프이고 두 번째 for 루프에서는 학생입니다. 같은 과목의 기록이 s1보다 높은 수치입니다. 이 숫자가 3보다 작으면 s1이 상위 3위 안에 든다는 의미입니다.

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

을 실행한 결과와 동일한 것을 확인할 수 있습니다.

3. 과목별 점수가 90점 이상인 학생의 기록을 조회해 보세요

테이블과 데이터는 모두 있습니다. 그런데 이 유형의 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
)

Query one 과목별 학생의 낙제 점수 기록

###查询一个学生每科分数不及格的记录
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, '数学', 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을 작성하는 방법을 구현합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!