MySQL 查詢平均分最低的班級：ORDER BY AVG() 直接搞定

單表 dd(user_id, class_no, score)，求平均分最低的班級：

SELECT class_no, AVG(score) AS avg_score
FROM dd
GROUP BY class_no
ORDER BY avg_score ASC
LIMIT 1;

要點

• GROUP BY class_no 分組。
• AVG(score) 計算組內平均。
• ORDER BY avg_score ASC 升序，最低在前。
• LIMIT 1 取第一條（最低）。

並列最低處理

若多班并列最低，需先求最低值再反查：

SELECT class_no, AVG(score) AS avg_score
FROM dd
GROUP BY class_no
HAVING avg_score = (SELECT MIN(avg_score) FROM (SELECT AVG(score) AS avg_score FROM dd GROUP BY class_no) t);

MySQL 8.0+ 窗口函數寫法

WITH cte AS (
  SELECT class_no, AVG(score) AS avg_score,
         RANK() OVER (ORDER BY AVG(score)) rk
  FROM dd GROUP BY class_no
)
SELECT class_no, avg_score FROM cte WHERE rk = 1;