SQL CASE 運算式、子查詢與 JOIN 連接查詢綜合實戰指南

CASE 運算式兩種語法

1. 簡單 CASE (等值比較，類似 Switch)

SELECT Name,
    CASE Level
        WHEN 1 THEN '骨灰'
        WHEN 2 THEN '大蝦'
        WHEN 3 THEN '菜鳥'
    END AS Rank
FROM [User];

2. 搜尋 CASE (布林運算式，類似 If-Else)

SELECT StudentID,
    CASE
        WHEN English >= 90 THEN '優'
        WHEN English >= 80 THEN '良'
        WHEN English >= 70 THEN '中'
        WHEN English >= 60 THEN '可'
        ELSE '差'
    END AS Grade
FROM Score
ORDER BY English;

3. 實用技巧：條件彙總 (行轉列)

SELECT Name,
    ISNULL(SUM(CASE Subject WHEN '語文' THEN Result END), 0) AS Chinese,
    ISNULL(SUM(CASE Subject WHEN '數學' THEN Result END), 0) AS Math,
    ISNULL(SUM(CASE Subject WHEN '物理' THEN Result END), 0) AS Physics
FROM StudentScore
GROUP BY Name;

子查詢分類與應用

1. 標量子查詢 (單值)，可用於 SELECT/WHERE/SELECT 列表

SELECT 1,
    (SELECT SUM(English) FROM Score) AS TotalEnglish,
    (SELECT AVG(Age) FROM Student) AS AvgAge;

2. 列子查詢 (單欄多列)，搭配 IN / NOT IN / ANY / ALL

-- 查詢高一一班、高二一班學生
SELECT * FROM Student 
WHERE ClassID IN (SELECT ClassID FROM Class WHERE ClassName IN ('高一一班','高二一班'));

-- 刪除劉備、關羽、張飛成績
DELETE FROM Score 
WHERE StudentID IN (SELECT StudentID FROM Student WHERE Name IN ('劉備','關羽','張飛'));

3. 關聯子查詢 (外部參考)，逐列評估

SELECT s.Name, 
    (SELECT COUNT(*) FROM Score WHERE StudentID = s.StudentID) AS ExamCount
FROM Student s;

4. 衍生表 (FROM 子查詢)，必須加別名

SELECT * FROM (SELECT * FROM Student) AS t;

分頁查詢演進

SQL Server 2000 風格 (NOT IN + TOP)

-- 第 1 頁 (前 3 筆)
SELECT TOP 3 * FROM Student ORDER BY StudentID DESC;

-- 第 2 頁
SELECT TOP 3 * FROM Student 
WHERE StudentID NOT IN (SELECT TOP 3 StudentID FROM Student ORDER BY StudentID DESC)
ORDER BY StudentID DESC;

SQL Server 2005+ 標準語法 (ROW_NUMBER)

DECLARE @PageSize INT = 3, @PageNum INT = 2;
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY StudentID DESC) AS RN, *
    FROM Student
) AS t
WHERE RN BETWEEN (@PageNum-1)*@PageSize + 1 AND @PageNum*@PageSize;

SQL Server 2012+ 簡潔語法 (OFFSET FETCH)

SELECT * FROM Student
ORDER BY StudentID DESC
OFFSET (@PageNum-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

JOIN 連接查詢完整類型

實戰範例：學生-班級多表查詢

-- 內連接：學生與班級資訊
SELECT s.Name, s.Age, c.ClassName
FROM Student s
INNER JOIN Class c ON s.ClassID = c.ClassID
WHERE s.Sex = '女';

-- 左連接：所有班級及其學生 (無學生班級也顯示)
SELECT c.ClassName, s.Name
FROM Class c
LEFT JOIN Student s ON c.ClassID = s.ClassID;

綜合練習：收入/支出分欄統計

CREATE TABLE Test (Number VARCHAR(10), Amount INT);
INSERT INTO Test VALUES ('RK1',10), ('RK2',20), ('RK3',-30), ('RK4',-10);

SELECT Number,
    CASE WHEN Amount > 0 THEN Amount ELSE 0 END AS Income,
    CASE WHEN Amount < 0 THEN ABS(Amount) ELSE 0 END AS Expense
FROM Test;

效能提示

• 子查詢優於關聯子查詢 (可改寫為 JOIN/APPLY)
• 大數據分頁優先使用 OFFSET FETCH 或 ROW_NUMBER，避免 NOT IN 掃描
• CASE 運算式在 SELECT 列表中執行成本極低，放心使用
• 確保 JOIN 條件欄位有索引，避免隱含轉換導致索引失效

掌握 CASE 條件邏輯、子查詢類型與 JOIN 連接語意，是編寫高效、可讀、可維護 SQL 的核心基礎。