SQL 統計查詢實戰大全：排名、分組、條件彙總完全攻略

前言

統計報表是資料庫應用核心場景。本文收錄 13 類高頻統計查詢模式，涵蓋總分排名、單科最高分、平均分、課程優秀率、橫向轉欄、Top-N 問題、中位數排名等，所有範例基於 stuscore(學生成績表) 表結構，可直接套用至實務專案。

假設表結構

stuscore (
    stuid   int,       -- 學號
    name    varchar,   -- 姓名
    subject varchar,   -- 科目：語文/數學/英語
    score   int        -- 分數
)

1. 每人總分排名

SELECT name, SUM(score) AS allscore
FROM stuscore
GROUP BY name
ORDER BY allscore DESC

2. 每人總分排名（含學號）

SELECT DISTINCT t1.name, t1.stuid, t2.allscore
FROM stuscore t1
JOIN (
    SELECT stuid, SUM(score) AS allscore
    FROM stuscore
    GROUP BY stuid
) t2 ON t1.stuid = t2.stuid
ORDER BY t2.allscore DESC

3. 每人單科最高分

SELECT t1.stuid, t1.name, t1.subject, t1.score
FROM stuscore t1
JOIN (
    SELECT stuid, MAX(score) AS maxscore
    FROM stuscore
    GROUP BY stuid
) t2 ON t1.stuid = t2.stuid AND t1.score = t2.maxscore

4. 每人平均分

SELECT DISTINCT t1.stuid, t1.name, t2.avgscore
FROM stuscore t1
JOIN (
    SELECT stuid, AVG(score) AS avgscore
    FROM stuscore
    GROUP BY stuid
) t2 ON t1.stuid = t2.stuid

5. 各科最高分學生

SELECT t1.stuid, t1.name, t1.subject, t2.maxscore
FROM stuscore t1
JOIN (
    SELECT subject, MAX(score) AS maxscore
    FROM stuscore
    GROUP BY subject
) t2 ON t1.subject = t2.subject AND t1.score = t2.maxscore

6. 各科前兩名學生（Top-N Per Group）

SELECT DISTINCT t1.*
FROM stuscore t1
WHERE t1.id IN (
    SELECT TOP 2 id
    FROM stuscore
    WHERE subject = t1.subject
    ORDER BY score DESC
)
ORDER BY t1.subject

7. 橫向轉欄：學號、姓名、語文、數學、英語、總分、平均分

SELECT 
    stuid AS 學號,
    name AS 姓名,
    SUM(CASE WHEN subject = '語文' THEN score ELSE 0 END) AS 語文,
    SUM(CASE WHEN subject = '數學' THEN score ELSE 0 END) AS 數學,
    SUM(CASE WHEN subject = '英語' THEN score ELSE 0 END) AS 英語,
    SUM(score) AS 總分,
    (SUM(score) / COUNT(*)) AS 平均分
FROM stuscore
GROUP BY stuid, name
ORDER BY 總分 DESC

8. 各科平均分

SELECT subject, AVG(score) AS avgscore
FROM stuscore
GROUP BY subject

9. 數學成績排名（兩種寫法）

法一：暫表 + 變數累加（相容舊版）

DECLARE @tmp TABLE (pm int, name varchar(50), score int, stuid int)
INSERT INTO @tmp SELECT NULL, name, score, stuid FROM stuscore WHERE subject = '數學' ORDER BY score DESC

DECLARE @id int = 0
UPDATE @tmp SET @id = @id + 1, pm = @id
SELECT * FROM @tmp

法二：視窗函數 DENSE_RANK（SQL 2005+ 推薦）

SELECT 
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
    name, subject, score, stuid
FROM stuscore
WHERE subject = '數學'
ORDER BY score DESC

10. 數學成績第 2-3 名學生

SELECT t3.*
FROM (
    SELECT TOP 2 t2.*
    FROM (
        SELECT TOP 3 name, subject, score, stuid
        FROM stuscore
        WHERE subject = '數學'
        ORDER BY score DESC
    ) t2
    ORDER BY t2.score ASC
) t3
ORDER BY t3.score DESC

11. 查詢特定學生（李四）數學排名

DECLARE @tmp TABLE (pm int, name varchar(50), score int, stuid int)
INSERT INTO @tmp SELECT NULL, name, score, stuid FROM stuscore WHERE subject = '數學' ORDER BY score DESC
DECLARE @id int = 0
UPDATE @tmp SET @id = @id + 1, pm = @id
SELECT * FROM @tmp WHERE name = '李四'

12. 各科等級統計：不及格(<60)、良(60-80)、優(>80)

SELECT 
    subject,
    (SELECT COUNT(*) FROM stuscore WHERE score < 60 AND subject = t1.subject) AS 不及格,
    (SELECT COUNT(*) FROM stuscore WHERE score BETWEEN 60 AND 80 AND subject = t1.subject) AS 良,
    (SELECT COUNT(*) FROM stuscore WHERE score > 80 AND subject = t1.subject) AS 優
FROM stuscore t1
GROUP BY subject

13. 彙總輸出：數學:張三(50分),李四(90分),王五(90分),趙六(76分)

DECLARE @s varchar(1000) = ''
SELECT @s = @s + ',' + name + '(' + CAST(score AS varchar(10)) + '分)'
FROM stuscore
WHERE subject = '數學'
SET @s = STUFF(@s, 1, 1, '')
PRINT '數學:' + @s

進階技巧總結

• 相關子查詢 vs JOIN：小結果集用相關子查詢可讀性高；大數據量 JOIN 效能穩定
• 視窗函數：ROW_NUMBER()、RANK()、DENSE_RANK() 解決 Top-N Per Group、排名並列問題，SQL 2005+ 首選
• 條件彙總：SUM(CASE WHEN ... THEN ... ELSE 0 END) 實現行轉列，避免多次掃描
• 字串彙總：變數累加 + STUFF 移除首分隔符，相容性最佳；SQL 2017+ 可用 STRING_AGG