SQL 查詢各科成績最高分學生資料：子查詢與 JOIN 實作

範例資料

Score 表：ID、UID、ScoreName、Score
User 表：ID、Name

SQL 敘述句

SELECT A.ID, U.Name, A.ScoreName, A.Score
FROM Score A, [User] U
WHERE A.UID IN (
    SELECT TOP 1 B.UID
    FROM Score B
    WHERE B.ScoreName = A.ScoreName
    ORDER BY B.Score DESC
)
AND A.UID = U.ID
ORDER BY A.ScoreName, A.Score DESC

邏輯解析

• 外層查詢遍歷每一筆 Score A。
• 相關子查詢針對同一 ScoreName，按分數降序取第一名的 UID。
• IN 判斷外層列的 UID 是否為該科最高分者。
• 最後 JOIN User 取得姓名，依科目、分數排序輸出。

查詢結果

效能優化建議

• 在 Score(ScoreName, Score DESC, UID) 建立複合索引，子查詢可直接 Index Seek + Top 1。
• 若資料量大，考慮使用 ROW_NUMBER() OVER (PARTITION BY ScoreName ORDER BY Score DESC) 窗口函數（SQL 2005+）單次掃描完成：

WITH Ranked AS (
    SELECT S.*, U.Name,
           ROW_NUMBER() OVER (PARTITION BY ScoreName ORDER BY Score DESC) AS rn
    FROM Score S JOIN [User] U ON S.UID = U.ID
)
SELECT ID, Name, ScoreName, Score
FROM Ranked
WHERE rn = 1
ORDER BY ScoreName