SQL Server 2000 經典實戰練習題全解析：從去重複到動態行轉列的 8 大核心場景

測試資料：「成績表」T

欄位：學生ID (F1)、學生姓名 (F2)、課程ID (F3)、課程名稱 (F4)、成績 (F5)、教師ID (F6)、教師姓名 (F7)。另有自增唯一鍵 F。

題目 1：刪除邏輯重複記錄（保留 F 最小者）

⭐ 最佳解：不等值自聯結

DELETE L
FROM 成績表 L
JOIN 成績表 R
  ON L.學生ID = R.學生ID
 AND L.課程ID = R.課程ID
 AND L.F > R.F;

原理：同一組 (學生ID, 課程ID) 中，F 值較大的被配對刪除，最小者無匹配而保留。效率高、語法標準。

替代方案：相關子查詢 / NOT IN

-- 相關子查詢版
DELETE T
FROM 成績表 T
WHERE F > (
    SELECT MIN(F)
    FROM 成績表 I
    WHERE I.學生ID = T.學生ID
      AND I.課程ID = T.課程ID
);

-- NOT IN 版（需注意 NULL 陷阱）
DELETE T
FROM 成績表 T
WHERE F NOT IN (
    SELECT MIN(F) FROM 成績表 GROUP BY 學生ID, 課程ID
);

調試技巧：先用 SELECT L.* 替換 DELETE L 預覽將刪除記錄。

題目 2：列印各科最高/最低分完整記錄（含師生資訊）

SELECT L.課程ID, L.課程名稱,
       L.成績 AS 最高分, L.學生ID, L.學生姓名, L.教師ID, L.教師姓名,
       R.成績 AS 最低分, R.學生ID, R.學生姓名, R.教師ID, R.教師姓名
FROM 成績表 L
JOIN 成績表 R ON L.課程ID = R.課程ID
WHERE L.成績 = (SELECT MAX(IL.成績) FROM 成績表 IL WHERE IL.課程ID = L.課程ID)
  AND R.成績 = (SELECT MIN(IR.成績) FROM 成績表 IR WHERE IR.課程ID = R.課程ID);

核心技巧：以課程為粒度，分別取得最高/最低分記錄，再自聯結合併師生資訊。

題目 3：學生四門課成績單（行轉列）

方案 A：相關子查詢（靜態課程）

SELECT 學生ID, MAX(學生姓名) AS 學生姓名,
       (SELECT 成績 FROM 成績表 WHERE 學生ID=T.學生ID AND 課程ID='K1') AS 數學,
       (SELECT 成績 FROM 成績表 WHERE 學生ID=T.學生ID AND 課程ID='K2') AS 語文,
       (SELECT 成績 FROM 成績表 WHERE 學生ID=T.學生ID AND 課程ID='K3') AS 英語,
       (SELECT 成績 FROM 成績表 WHERE 學生ID=T.學生ID AND 課程ID='K4') AS 政治,
       COUNT(*) AS 有效課程數, AVG(T.成績) AS 平均成績
FROM 成績表 T
GROUP BY 學生ID
ORDER BY 平均成績 DESC;

方案 B：CASE 聚合（效能最佳、可區分 NULL/0）

SELECT 學生ID, MIN(學生姓名),
       SUM(CASE 課程ID WHEN 'K1' THEN 成績 END) AS 數學,
       SUM(CASE 課程ID WHEN 'K2' THEN 成績 END) AS 語文,
       SUM(CASE 課程ID WHEN 'K3' THEN 成績 END) AS 英語,
       SUM(CASE 課程ID WHEN 'K4' THEN 成績 END) AS 政治,
       COUNT(*) AS 有效課程數, AVG(成績) AS 平均成績
FROM 成績表
GROUP BY 學生ID
ORDER BY 平均成績 DESC;

方案 C：多重 LEFT JOIN（標準關聯寫法）

SELECT T.學生ID, MAX(T.學生姓名),
       MAX(T1.成績) AS 數學, MAX(T2.成績) AS 語文,
       MAX(T3.成績) AS 英語, MAX(T4.成績) AS 政治,
       COUNT(T.課程ID) AS 有效課程數,
       (ISNULL(MAX(T1.成績),0)+ISNULL(MAX(T2.成績),0)+ISNULL(MAX(T3.成績),0)+ISNULL(MAX(T4.成績),0))
       / COUNT(T.課程ID) AS 有效平均分
FROM 成績表 T
LEFT JOIN 成績表 T1 ON T.學生ID=T1.學生ID AND T1.課程ID='K1'
LEFT JOIN 成績表 T2 ON T.學生ID=T2.學生ID AND T2.課程ID='K2'
LEFT JOIN 成績表 T3 ON T.學生ID=T3.學生ID AND T3.課程ID='K3'
LEFT JOIN 成績表 T4 ON T.學生ID=T4.學生ID AND T4.課程ID='K4'
GROUP BY T.學生ID
ORDER BY 有效平均分 DESC;

動態 SQL 擴展：若課程不固定，可用游標遍歷課程表動態拼裝 CASE 或 JOIN 片段。

題目 4/5：各科平均分與及格率（行式 vs 列式呈現）

行式（動態、易擴展）

SELECT 課程ID, MAX(課程名稱) AS 課程名稱,
       AVG(成績) AS 平均成績,
       STR(100 * SUM(CASE WHEN 成績>=60 THEN 1 ELSE 0 END)/COUNT(*)) + '%' AS 及格百分比
FROM 成績表
GROUP BY 課程ID
ORDER BY 及格百分比 DESC;

列式（靜態、報表常用）

SELECT 
  SUM(CASE WHEN 課程ID='K1' THEN 成績 END)/SUM(CASE WHEN 課程ID='K1' THEN 1 END) AS 數學平均分,
  100 * SUM(CASE WHEN 課程ID='K1' AND 成績>=60 THEN 1 END)/SUM(CASE WHEN 課程ID='K1' THEN 1 END) AS 數學及格百分數,
  ... -- K2, K3, K4 同理
FROM 成績表;

題目 6：師課平均分排名（含去極值平均）

-- 一般平均
SELECT 教師ID, MAX(教師姓名), 課程ID, MAX(課程名稱), AVG(成績) AS 平均分
FROM 成績表
GROUP BY 教師ID, 課程ID
ORDER BY 平均分 DESC;

-- 去掉最高/最低分後平均（需至少 3 筆）
SELECT 教師ID, MAX(教師姓名), 課程ID, MAX(課程名稱),
       (SUM(成績)
        - (SELECT MAX(成績) FROM 成績表 WHERE 課程ID=T1.課程ID AND 教師ID=T1.教師ID)
        - (SELECT MIN(成績) FROM 成績表 WHERE 課程ID=T1.課程ID AND 教師ID=T1.教師ID)
       ) / CAST((SELECT COUNT(*)-2 FROM 成績表 WHERE 課程ID=T1.課程ID AND 教師ID=T1.教師ID) AS FLOAT) AS 平均分
FROM 成績表 T1
WHERE (SELECT COUNT(*)-2 FROM 成績表 WHERE 課程ID=T1.課程ID AND 教師ID=T1.教師ID) > 0
GROUP BY 教師ID, 課程ID
ORDER BY 平均分 DESC;

題目 7：分頁查詢——第 10~15 名學生成績單

現代寫法（SQL 2012+）： OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY。

題目 8：各科分數段統計（CASE 橫向聚合）

SELECT 課程ID, 課程名稱,
       SUM(CASE WHEN 成績 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85],
       SUM(CASE WHEN 成績 BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS [85-70],
       SUM(CASE WHEN 成績 BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS [70-60],
       SUM(CASE WHEN 成績 < 60 THEN 1 ELSE 0 END) AS [60以下]
FROM 成績表
GROUP BY 課程ID, 課程名稱;

陷阱提醒： BETWEEN 85 AND 100 正確；若寫 BETWEEN 100 AND 85 會得到空集合（下界 > 上界）。