SQL GROUP BY 進階：查詢每系最高分學生學號、姓名、成績的正確寫法

SQL 中 `GROUP BY` 僅能直接選取「分組欄位」與「聚合函式」，若需同時顯示非聚合欄位（如學號、姓名），必須用**子查詢**、**視窗函式**或**關聯子查詢**。本文以「查詢 CS 系最高分學生」與「查詢每系最高分學生」為例，詳細對比錯誤寫法與三種標準解法。

資料表結構

student(sno, sname, sdept)
course(cno, cname, ccredit)
sc(sno, cno, grade)

錯誤寫法分析

-- 錯誤 1：子查詢引用外部表別名但未關聯
SELECT student.sno, sname, grade
FROM student, sc
WHERE student.sno = sc.sno
  AND grade = (SELECT MAX(grade) FROM sc WHERE sc.sno = sno AND sdept = 'cs');
-- ❌ 子查詢中 sc 表沒有 sdept 欄位，sdept 在 student 表

-- 錯誤 2：GROUP BY 加入 sno 導致每組只有一筆
SELECT sdept, sc.sno, MAX(grade)
FROM sc, student
WHERE sc.sno = student.sno
GROUP BY sdept, sc.sno;
-- ❌ 分組變成 (系別, 學生)，MAX(grade) 只是該生最高分，非系最高分

解法一：關聯子查詢（相容性最好，所有資料庫支援）

-- 查詢 CS 系最高分學生
SELECT s.sno, s.sname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE s.sdept = 'cs'
  AND sc.grade = (
      SELECT MAX(sc2.grade)
      FROM sc sc2
      JOIN student s2 ON sc2.sno = s2.sno
      WHERE s2.sdept = 'cs'
  );

-- 查詢每系最高分學生（可能多名同分）
SELECT s.sdept, s.sno, s.sname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
WHERE sc.grade = (
    SELECT MAX(sc2.grade)
    FROM sc sc2
    JOIN student s2 ON sc2.sno = s2.sno
    WHERE s2.sdept = s.sdept
);

解法二：視窗函式 `ROW_NUMBER()`（SQL Server / PostgreSQL / Oracle / MySQL 8.0+）

WITH ranked AS (
    SELECT s.sdept, s.sno, s.sname, sc.grade,
           ROW_NUMBER() OVER (PARTITION BY s.sdept ORDER BY sc.grade DESC) AS rn
    FROM student s
    JOIN sc ON s.sno = sc.sno
)
SELECT sdept, sno, sname, grade
FROM ranked
WHERE rn = 1;  -- 若要保留同分並列用 RANK() 或 DENSE_RANK()

解法三：派生表 / CTE 先算每系最高分，再 JOIN 回學生表

-- 步驟 1：每系最高分
WITH dept_max AS (
    SELECT s.sdept, MAX(sc.grade) AS max_grade
    FROM student s
    JOIN sc ON s.sno = sc.sno
    GROUP BY s.sdept
)
-- 步驟 2：JOIN 回學生表取學號姓名
SELECT s.sdept, s.sno, s.sname, dm.max_grade AS grade
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN dept_max dm ON s.sdept = dm.sdept AND sc.grade = dm.max_grade;

三種解法對比

關鍵原則

• `GROUP BY` 只能直接輸出「分組鍵」+「聚合值」
• 需顯示非聚合欄位 → **必須二次查詢**（子查詢/視窗函式/CTE JOIN）
• 「每組 Top-N」問題統一用 `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)`
• 若允許同分並列 → 用 `RANK()` 或 `DENSE_RANK()`

依據資料庫版本與資料量選擇解法：現代資料庫首選視窗函式，舊版用關聯子查詢或 CTE JOIN，核心邏輯都是「先算每組聚合值，再關聯回明細表」。