在多到多的关系中，每个班获得前5名学生。
在多到多的关系中，每个班获得前5名学生。
提问于 2020-08-12 01:13:13
所以我在MySQL上做这个。
我有桌子、学生和班级。有一个多到多的关系，其中我跟踪通过student_class_rel表。下面是表格和它们的列。
学生
student_id | name | email | GPA
类
class_id | name | room
student_class_rel
student_id | class_id
我想得到一个student_id的每一个班级的5个学生与最高的GPA(连同班级信息)。我该怎么做？
例如，该查询获取100个类，然后为该类中的所有学生获取每个student_id。
SELECT *,
JSON_ARRAYAGG(student_id)
FROM classes
INNER JOIN student_class_rel
USING(class_id)
GROUP BY class_id;
LIMIT 100我想要这个查询，期望JSON_ARRAYAGG(student_id)只在每个班而不是每个学生的GPA上保存前5名学生的ID。
另外，假设有超过数百万的学生和数十万的班级，并且每个班级大约有50到1000名学生。因此，性能是此查询的关键。
我也知道黑客类型的解决方案(比如对每个类进行一次多次查询)，但到目前为止，这些方法太慢了。我花了太多的时间想办法解决这个问题，任何帮助都是非常感谢的！(如果还需要更多的信息，请告诉我)。
回答 1
Database Administration用户
回答已采纳
发布于 2020-08-12 04:51:03
- 具有GPAs的学生是一个聚合。
- 类信息不是聚合。
解决方案：
- 使用两个查询
- 查询GPAs (CTE和Window函数)
- 查询类信息
您可能需要在student_id和class_id上使用两个索引来提高性能。
MySQL 8.0.20中的示例查询：
WITH ordered_gpa AS(
SELECT students.id,
students.gpa,
student_class_rel.class_id,
rank() over (partition by student_class_rel.class_id order by students.gpa DESC) gpa_rank
FROM student_class_rel
INNER JOIN students
ON student_class_rel.stu_id = students.id
)
SELECT *
FROM ordered_gpa
WHERE gpa_rank <= 5;在这里，我们使用Windows而不是GROUP BY，后者是拆分行的PARTITION BY。
SELECT class_id, name, room
FROM classes
WHERE class_id IN (
SELECT DISTINCT class_id
FROM student_class_rel
) tmp;您可以将此查询与第一个查询结合起来。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/273608
复制相关文章
相似问题
腾讯云开发者
