您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL 進階查詢實戰解析：CASE 條件彙總、CHARINDEX 字串分組、遞歸 CTE 階層顯示

編輯：關於SqlServer

三道經典 SQL 進階題目，展示條件彙總、字串分割統計、遞歸階層顯示的核心技巧與思維路徑。

題目一：依標記分欄彙總（收款/退款）

需求：同一資料表含 標記（收/退）、收款員、金額，輸出每位收款員的收款合計與退款合計。

核心技巧：CASE WHEN ... THEN ... ELSE 0 END 搭配 SUM() 進行條件彙總。

WITH ta AS (
    SELECT '收' AS 標記, '100' AS 收款員, 150 AS 金額 UNION ALL
    SELECT '收', '100', 375 UNION ALL
    SELECT '退', '100', 78 UNION ALL
    SELECT '收', '200', 74
)
SELECT 
    收款員,
    SUM(CASE WHEN 標記 = '收' THEN 金額 ELSE 0 END) AS 收款合計,
    SUM(CASE WHEN 標記 = '退' THEN 金額 ELSE 0 END) AS 退款合計
FROM ta
GROUP BY 收款員;

延伸：PIVOT 亦可實現，但 CASE 彈性更高、可攜性更好（跨資料庫）。

題目二：字串欄位分組統計（CHARINDEX 應用）

需求：表 B 儲存任務，ID 為逗號分隔字串（如 '1,2,3'），表 A 為員工清單。統計每位員工參與任務數。

核心技巧：CHARINDEX(B.ID, A.ID) > 0 判斷員工 ID 是否出現在任務字串中。

WITH TA AS (
    SELECT '1,2' AS ID, 'job1' AS Task UNION ALL
    SELECT '1,2,3', 'job3' UNION ALL
    SELECT '2,3', 'job2' UNION ALL
    SELECT '3,4,5', 'job4'
), TB AS (
    SELECT '1' AS ID, '張三' AS Name UNION ALL
    SELECT '2', '王二' UNION ALL
    SELECT '3', '李四' UNION ALL
    SELECT '4', '李明' UNION ALL
    SELECT '5', '王五'
)
SELECT B.ID, B.Name, COUNT(1) AS Tasks
FROM TA A, TB B
WHERE CHARINDEX(B.ID, A.ID) > 0
GROUP BY B.ID, B.Name
ORDER BY B.ID;

資料模型警示：以逗號分隔字串儲存多值**違反第一正規化（1NF）**，查詢效能差、難維護。正規化設計應建立中介表 EmployeeTask(EmployeeID, TaskID)。

題目三：行政區域碼遞歸階層顯示（CTE 遞歸）

需求：code 六位碼（前二省、中二市、後二縣），CCode 指向上層代碼。輸出完整層級路徑：省 → 省,市 → 省,市,縣。

核心技巧：遞歸 CTE 逐層向上追溯，或如原文分三層 CTE (ta 省、tb 市、tc 縣) 聯集。

WITH TA AS (
    SELECT code, city, CCode, CAST(city AS NVARCHAR(MAX)) AS Content
    FROM City WHERE RIGHT(code, 4) = '0000'  -- 省級
), TB AS (
    SELECT b.code, b.city, b.CCode, a.Content + N',' + b.city AS Content
    FROM TA a JOIN City b ON LEFT(a.CCode, 2) = LEFT(b.CCode, 2)
    WHERE RIGHT(b.code, 2) = '00' AND RIGHT(b.code, 4) <> '0000'  -- 市級
), TC AS (
    SELECT c.code, c.city, c.CCode, b.Content + N',' + c.city AS Content
    FROM TB b JOIN City c ON LEFT(b.CCode, 4) = LEFT(c.CCode, 4)
    WHERE RIGHT(c.code, 2) <> '00'  -- 縣級
)
SELECT code, city, CCode, Content FROM TA
UNION ALL SELECT code, city, CCode, Content FROM TB
UNION ALL SELECT code, city, CCode, Content FROM TC
ORDER BY code;

現代寫法：遞歸 CTE 通用解法

WITH RECURSIVE Hierarchy AS (
    -- Anchor: 根節點（省級）
    SELECT code, city, CCode, CAST(city AS NVARCHAR(MAX)) AS Path, 1 AS Level
    FROM City WHERE RIGHT(code, 4) = '0000'
    UNION ALL
    -- Recursive: 子節點
    SELECT c.code, c.city, c.CCode, h.Path + N',' + c.city, h.Level + 1
    FROM City c JOIN Hierarchy h ON LEFT(h.CCode, 2 * h.Level) = LEFT(c.CCode, 2 * h.Level)
    WHERE h.Level < 3
)
SELECT code, city, CCode, Path FROM Hierarchy ORDER BY code;

遞歸 CTE 碼更簡潔、層級可擴充，為 SQL:1999 標準（SQL Server 2005+ 支援）。