萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server CASE WHEN 解決多條件模糊查詢:告別動態 SQL 拼接與注入風險

多條件模糊查詢的傳統痛點

在項目開發中,多條件模糊查詢是高頻需求。傳統方案主要有兩種:

  1. 應用層拼接 SQL:根據條件動態構建 WHERE 子句,易產生 SQL 注入漏洞,維護困難
  2. 存儲過程動態 SQL:本質同上,只是將拼接邏輯移至資料庫層,同樣存在注入風險與性能開銷

兩種方式在條件較多時需編寫大量 IF 判斷,代碼冗餘且難以優化執行計劃。

優雅解決方案:CASE WHEN + CHARINDEX

利用 CASE WHEN 邏輯判斷結合 CHARINDEX 字符串搜尋,可在單條靜態 SQL 中實現動態條件過濾,完全避免拼接與注入風險。

核心邏輯原理

  • 參數為空字符串('')時,條件恆為真(忽略該過濾條件)
  • 參數非空時,執行模糊匹配
  • 使用 CHARINDEX 替代 LIKE 避免通配符拼接

實戰代碼演示(Northwind Employees 表)

DECLARE @FirstName NVARCHAR(50) = '', @LastName NVARCHAR(50) = '';

SELECT * FROM Employees
WHERE CHARINDEX(
    CASE WHEN @FirstName = '' THEN FirstName ELSE @FirstName END,
    FirstName
) > 0
AND CHARINDEX(
    CASE WHEN @LastName = '' THEN LastName ELSE @LastName END,
    LastName
) > 0;

執行效果驗證

場景@FirstName@LastName結果說明
全量查詢''''返回所有記錄(條件恆真)
單條件'n'''FirstName 包含 'n' 的記錄
雙條件'n''d'FirstName 包含 'n' 且 LastName 包含 'd'

CASE WHEN 兩種語法形式

1. 簡單 CASE 函數(等值判斷)

CASE sex
    WHEN 'M' THEN '男'
    WHEN 'F' THEN '女'
    ELSE '其他'
END

2. CASE 搜索函數(條件表達式,功能更強)

CASE
    WHEN sex = 'M' THEN '男'
    WHEN sex = 'F' THEN '女'
    ELSE '其他'
END

關鍵差異:搜索函數支援任意布林表達式(如範圍判斷 salary > 5000),簡單函數僅支援等值匹配。

⚠️ 重要陷阱:條件順序決定結果

CASE 只返回第一個滿足條件的分支,後續分支自動忽略:

-- 錯誤示範:永遠無法返回 '第二類'
CASE
    WHEN col_ IN ('a', 'b') THEN '第一類'
    WHEN col_ IN ('a') THEN '第二類'  -- 永不執行,因 'a' 已被第一條捕獲
    ELSE '其他'
END

修正原則:將具體條件放在前面,通用條件放在後面。

進階應用場景

場景一:動態分組統計(國家 → 洲際分組)

SELECT 
    CASE country
        WHEN '中國' THEN '亞洲'
        WHEN '印度' THEN '亞洲'
        WHEN '日本' THEN '亞洲'
        WHEN '美國' THEN '北美洲'
        WHEN '加拿大' THEN '北美洲'
        WHEN '墨西哥' THEN '北美洲'
        ELSE '其他'
    END AS continent,
    SUM(population) AS total_population
FROM Table_A
GROUP BY 
    CASE country
        WHEN '中國' THEN '亞洲'
        WHEN '印度' THEN '亞洲'
        WHEN '日本' THEN '亞洲'
        WHEN '美國' THEN '北美洲'
        WHEN '加拿大' THEN '北美洲'
        WHEN '墨西哥' THEN '北美洲'
        ELSE '其他'
    END;

場景二:薪資分級統計

SELECT 
    CASE
        WHEN salary <= 3000 THEN '初級'
        WHEN salary > 3000 AND salary <= 6000 THEN '中級'
        WHEN salary > 6000 AND salary <= 10000 THEN '高級'
        WHEN salary > 10000 THEN '專家'
        ELSE '未定級'
    END AS salary_class,
    COUNT(*) AS emp_count
FROM Employees
GROUP BY 
    CASE
        WHEN salary <= 3000 THEN '初級'
        WHEN salary > 3000 AND salary <= 6000 THEN '中級'
        WHEN salary > 6000 AND salary <= 10000 THEN '高級'
        WHEN salary > 10000 THEN '專家'
        ELSE '未定級'
    END;

場景三:樞紐分析(行轉列)

SELECT 
    country,
    SUM(CASE WHEN sex = 'M' THEN population ELSE 0 END) AS male_pop,
    SUM(CASE WHEN sex = 'F' THEN population ELSE 0 END) AS female_pop
FROM Demographics
GROUP BY country;

單條 SQL 完成二維交叉表輸出,避免 UNION ALL 多次掃描表。

場景四:CHECK 約束中的條件驗證

需求:女員工薪資必須 > 5000,男員工無限制

-- 使用 CASE 實現條件約束
CONSTRAINT chk_salary CHECK (
    CASE 
        WHEN sex = 'F' THEN 
            CASE WHEN salary > 5000 THEN 1 ELSE 0 END
        ELSE 1  -- 男員工自動通過
    END = 1
)

-- 對比:純 CHECK 無法實現條件邏輯
-- CONSTRAINT chk_salary CHECK (sex = 'F' AND salary > 5000)  -- 會導致男員工無法插入

個人常用片段

-- 動態字段別名顯示
SELECT 
    (CASE WHEN t.name = 'target' THEN 'ok' ELSE 'no' END) AS myCom,
    jname 
FROM t;

總結

CASE WHEN 是 T-SQL 中實現條件邏輯的核心工具,善用它可將複雜的動態查詢轉化為靜態參數化 SQL,同時解決注入風險、執行計劃復用、代碼維護性等多重問題。掌握其兩種語法形式、條件順序陷阱、以及在分組、樞紐、約束中的進階用法,將顯著提升 SQL 開發效率與質量。

copyright © 萬盛學電腦網 all rights reserved