SQL Server 查詢學生平均成績並排名：子查詢與 DENSE_RANK 兩種寫法

需求

學生表 Student、成績表 SC。查詢每位學生平均分並排名，處理同分同名次 (密集排名) 與同分留空名次 (競爭排名) 兩種業務規則。

表結構

Student (S#, Sname, Sage, Ssex)
SC      (S#, C#, score)

寫法一：競爭排名 (同分留空名次) - 相關子查詢

SELECT t1.*,
       px = (SELECT COUNT(1)
             FROM (
                 SELECT m.S# AS 學生編號,
                        m.Sname AS 學生姓名,
                        ISNULL(CAST(AVG(score) AS DECIMAL(18,2)), 0) AS 平均成績
                 FROM Student m
                 LEFT JOIN SC n ON m.S# = n.S#
                 GROUP BY m.S#, m.Sname
             ) t2
             WHERE 平均成績 > t1.平均成績) + 1
FROM (
    SELECT m.S# AS 學生編號,
           m.Sname AS 學生姓名,
           ISNULL(CAST(AVG(score) AS DECIMAL(18,2)), 0) AS 平均成績
    FROM Student m
    LEFT JOIN SC n ON m.S# = n.S#
    GROUP BY m.S#, m.Sname
) t1
ORDER BY px;

• 邏輯：統計有多少人的平均分 嚴格大於 當前行，名次 = 該數 + 1。
• 同分者名次相同，下一名次跳過 (如 1, 2, 2, 4)。

寫法二：密集排名 (同分同名次，不留空) - DISTINCT 計數

SELECT t1.*,
       px = (SELECT COUNT(DISTINCT 平均成績)
             FROM (
                 SELECT m.S# AS 學生編號,
                        m.Sname AS 學生姓名,
                        ISNULL(CAST(AVG(score) AS DECIMAL(18,2)), 0) AS 平均成績
                 FROM Student m
                 LEFT JOIN SC n ON m.S# = n.S#
                 GROUP BY m.S#, m.Sname
             ) t2
             WHERE 平均成績 >= t1.平均成績)
FROM (
    SELECT m.S# AS 學生編號,
           m.Sname AS 學生姓名,
           ISNULL(CAST(AVG(score) AS DECIMAL(18,2)), 0) AS 平均成績
    FROM Student m
    LEFT JOIN SC n ON m.S# = n.S#
    GROUP BY m.S#, m.Sname
) t1
ORDER BY px;

• 邏輯：統計有多少 不同的平均分值 大於或等於 當前行。
• 同分者名次相同，下一名次緊接 (如 1, 2, 2, 3)。

現代寫法 (SQL Server 2012+)：窗口函數一行搞定

SELECT S#, Sname,
       AVG(CAST(score AS DECIMAL(18,2))) OVER (PARTITION BY S#) AS 平均成績,
       RANK()       OVER (ORDER BY AVG(score) DESC) AS 競爭排名,      -- 1,2,2,4
       DENSE_RANK() OVER (ORDER BY AVG(score) DESC) AS 密集排名      -- 1,2,2,3
FROM Student s
LEFT JOIN SC sc ON s.S# = sc.S#
GROUP BY S#, Sname;

優勢：可讀性高、效能優 (單掃描)、無需重複衍生表。

排名函數對照

SQL Server 2000/2005 專案維護期可用子查詢寫法；新專案請直接使用窗口函數，符合 ANSI SQL 標準且效能最佳。