SQL 查詢平均分最高的系：窗口函數 DENSE_RANK 實作

表結構

• `stu(sno, sname, sdept, sage)`
• `sc(sno, cno, grade)`
• `cou(cno, cname)`

需求

查詢平均分最高的系（sdept），若有並列顯示所有。

標準 SQL 解法 (支援窗口函數的資料庫：PostgreSQL, SQL Server, Oracle, MySQL 8.0+)

WITH dept_avg AS (
    SELECT 
        s.sdept,
        AVG(sc.grade) AS avg_grade
    FROM stu s
    JOIN sc ON sc.sno = s.sno
    GROUP BY s.sdept
),
ranked AS (
    SELECT 
        sdept,
        avg_grade,
        DENSE_RANK() OVER (ORDER BY avg_grade DESC) AS rnk
    FROM dept_avg
)
SELECT sdept, avg_grade
FROM ranked
WHERE rnk = 1;

MySQL 5.7 相容寫法 (無窗口函數)

SELECT d.sdept, d.avg_grade
FROM (
    SELECT s.sdept, AVG(sc.grade) AS avg_grade
    FROM stu s JOIN sc ON sc.sno = s.sno
    GROUP BY s.sdept
) d
JOIN (
    SELECT MAX(avg_grade) AS max_avg
    FROM (
        SELECT AVG(sc.grade) AS avg_grade
        FROM stu s JOIN sc ON sc.sno = s.sno
        GROUP BY s.sdept
    ) t
) m ON d.avg_grade = m.max_avg;

關鍵點

1. 先按學生聚合再按系聚合，避免一門課多次計入同一學生。
2. `DENSE_RANK` 保留並列名次，`RANK` 會產生間隙。
3. CTE (WITH 子句) 提升可讀性，舊版本可用子查詢或臨時表替代。

結語

窗口函數讓「分組內排序取 Top-N」變得直觀高效，是現代 SQL 開發必備技能。