在多對多關係中獲得每個班級的前 5 名學生

所以我在 MySQL 中這樣做。

我有表學生和班級。我通過 student_class_rel 表跟踪它們之間的多對多關係。以下是表格及其列。

---

學生們

student_id | name | email | GPA

---

班級

class_id | name | room

---

student_class_rel

student_id | class_id

---

我想獲得 GPA 最高的每個班級的 5 名學生的 student_id（以及班級資訊）。我該怎麼做？

例如，此查詢獲取 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，而不是每個學生的 ID。

此外，假設可能有數百萬學生和數十萬個班級，每個班級大約有 50 到 1000 名學生。所以性能是這個查詢的關鍵。

而且我知道 hacky 類型的解決方案（比如進行多個查詢，每個類一次），但到目前為止，這些解決方案太慢了。我花了太多時間試圖弄清楚這一點，非常感謝任何幫助！（如果需要任何其他資訊，也請告訴我）。

• 有 GPA 的學生是一個聚合體
• 類資訊不是聚合

解決方案：

• 使用兩個查詢
• 查詢 GPA（CTE 和視窗函式）
• 查詢班級資訊

您可能需要兩個索引來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;

您可以將此查詢與第一個查詢結合使用。

引用自：https://dba.stackexchange.com/questions/273608