MySql 寻找同一类别下某个排序第一的数据

常规话题 快问快答
1

佬们，问个sql问题
course name score
语文 y 91
数学 y 89
语文 x 90
数学 x 95
找到每门课成绩最高的人
SELECT course,substring_index(group_concat(name order by score desc),‘,’,1) name
FROM table
GROUP BY course;
但是不太想用substring_index(group_concat()) 很多的话，记得group_concat有长度限制 问问其他的
记得之前搜到过一个方法，当时是5.6不支持，现在升到5.7了，也忘了啥方法了，不知道支持吗

2 个赞
2 
select course_name, name, max(score) as max_score
from courses
group by course_name
having score = max(score);
5 个赞
3

假如复杂一点 一个是max(score)的name, 另一个是min(score)的name呢，写两个sql？

1 个赞
4

低版本(8一下) 你可以写两个sql话
想写一个的话 就子查询嵌套一下

8以上直接用 first_value

1 个赞
5

难顶，为啥5.7不支持 :sob:

1 个赞
6

用UNION ALL直接变一个

1 个赞
7

因为是从8开始支持的

8

你别说 确实是个简单的办法

9

SELECT course_name, MAX(score) AS max_score
FROM your_table_name
GROUP BY course_name;

SELECT t1.course_name, t1.score, t1.student_name
FROM your_table_name t1
JOIN (
SELECT course_name, MAX(score) AS max_score
FROM your_table_name
GROUP BY course_name
) t2 ON t1.course_name = t2.course_name AND t1.score = t2.max_score;

1 个赞
10

public static void main(String args) {
List courses = Arrays.asList(
new Course(“语文”, “y”, 91),
new Course(“数学”, “y”, 89),
new Course(“语文”, “x”, 90),
new Course(“数学”, “x”, 95)
);

    Map<String, Optional<Course>> maxScores = courses.stream()
            .collect(Collectors.groupingBy(Course::getCourseName,
                    Collectors.maxBy(Comparator.comparingInt(Course::getScore))));

    Map<String, Course> topStudents = maxScores.entrySet().stream()
            .collect(Collectors.toMap(Map.Entry::getKey, entry -> entry.getValue().orElse(null)));

    System.out.println("每门课程的最高分数和对应的学生：");
    topStudents.forEach((course, student) -> System.out.println(course + ": " + student.getScore() + " (学生：" + student.getStudentName() + ")"));
}

static class Course {
    private String courseName;
    private String studentName;
    private int score;

    public Course(String courseName, String studentName, int score) {
        this.courseName = courseName;
        this.studentName = studentName;
        this.score = score;
    }

    public String getCourseName() {
        return courseName;
    }

    public String getStudentName() {
        return studentName;
    }

    public int getScore() {
        return score;
    }
}
11

gpt太强了

12 
select 
    course_name,
    score
from (
  select 
    *,
    dense_rank over (partition by course_name order by score desc) as rank
  from courses
) t where rank = 1;
1 个赞
13

mysql8 使用窗口函数

14

GPT一把梭哈