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

SQL 語法全面進階 (五)：篩選運算子、字串比對、彙總函數與子查詢

編輯：關於SqlServer

查詢核心武器庫：BETWEEN、LIKE、彙總函數、多層子查詢完整實戰

掌握精準篩選、模糊比對、統計計算與巢狀查詢，是撰寫高效 SQL 的關鍵。本文系統整理五大運算子/函數族群，附豐富模式範例與效能提示。

一、BETWEEN ... AND — 範圍篩選

-- 包含邊界值
SELECT * FROM Employee WHERE Age BETWEEN 25 AND 30;
-- 等價於 Age >= 25 AND Age <= 30

-- 日期範圍 (注意時間元件)
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01';  -- 推薦寫法，避免 BETWEEN 時元件陷阱

效能：可用索引 Seek (叢集/非叢集)，優於 IN 列舉大量離散值。

二、LIKE — 模式比對

萬用字元完整對照

模式	含義	範例符合	範例不符合
`%`	任意長度字串 (含空字串)	`'abc', 'a', ''`	-
`_`	單一字元	`'a', '1', '中'`	`'ab', ''`
`[範圍]`	單一字元在集合內	`'a', 'b', 'c'` (模式 `[a-c]`)	`'d', '1'`
`[^範圍]`	單一字元不在集合內	`'d', '1'` (模式 `[^a-c]`)	`'a', 'b'`
`逸出字元`	搜尋萬用字元本身	`'50\%' ESCAPE '\'` 找 "50%"	-

實戰模式範例

-- 1. 前綴/後綴/包含
WHERE Name LIKE '張%'      -- 姓張
WHERE Code LIKE '%-2024'   -- 以 -2024 結尾
WHERE Memo LIKE '%錯誤%'    -- 包含關鍵字

-- 2. 固定格式 (如身分證、電話)
WHERE ID LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

-- 3. 排除特定字元
WHERE Code NOT LIKE '%[^0-9]%'  -- 純數字代碼

-- 4. 大小寫敏感 (依定序)
WHERE Password COLLATE Latin1_General_CS_AS LIKE '%[A-Z]%'  -- 強制至少一大寫

效能警示：LIKE '%關鍵字%' 開頭萬用字元 → 全表/索引掃描。解法：全文檢索、前綴索引、應用層分詞。

三、彙總函數 — 統計計算

五大核心函數

函數	用途	關鍵行為	範例
`AVG(expr)`	算術平均	忽略 NULL；數值類型回傳同類型	`AVG(Score)`
`COUNT(expr)`	非 NULL 計數	`COUNT(*)` 計列數；`COUNT(DISTINCT col)` 去重	`COUNT(DISTINCT CustomerID)`
`SUM(expr)`	總和	忽略 NULL；溢位回傳錯誤	`SUM(Qty * UnitPrice)`
`MAX/MIN(expr)`	極值	支援字串/日期/數值	`MAX(OrderDate)`

進階：FIRST/LAST (Access/舊版相容)

SQL Server 無內建 FIRST/LAST，改用：

-- FIRST: 最早一筆
SELECT TOP 1 Col FROM Table ORDER BY SortCol ASC;
-- LAST: 最後一筆
SELECT TOP 1 Col FROM Table ORDER BY SortCol DESC;

四、多層子查詢 (Subqueries) — 巢狀邏輯

三大語法形式

形式	語法	回傳	典型用途
標量子查詢	`(SELECT MAX(Price) FROM Products)`	單值	SELECT 列、WHERE 比較、CASE 分支
列子查詢	`WHERE ID IN (SELECT CustomerID FROM Orders)`	單欄多列	`IN / NOT IN / ANY / ALL`
表子查詢	`FROM (SELECT ...)` / `JOIN (SELECT ...)`	多欄多列	衍生表、CTE 替代、複雜邏輯隔離

關鍵運算子

-- ANY / SOME: 符合任一
WHERE Price > ANY (SELECT Price FROM DiscountedProducts)

-- ALL: 符合全部
WHERE Price > ALL (SELECT Price FROM CompetitorProducts)

-- EXISTS: 存在性檢查 (通常最佳效能)
WHERE EXISTS (SELECT 1 FROM OrderDetails WHERE OrderID = O.OrderID)

-- NOT EXISTS: 反存在性 (防 NULL 陷阱優於 NOT IN)
WHERE NOT EXISTS (SELECT 1 FROM Returns WHERE OrderID = O.OrderID)

子查詢 vs JOIN 效能迷思

現代優化器常將子查詢轉換為等價 JOIN 計劃
EXISTS 通常優於 IN (特別是大結果集、含 NULL)
相關子查詢 (Correlated) 逐列執行 → 效能風險高，優先改寫為 JOIN/APPLY

五、綜合實戰：銷售儀表板查詢

WITH MonthlySales AS (
    SELECT 
        YEAR(OrderDate) AS Yr,
        MONTH(OrderDate) AS Mo,
        SUM(TotalAmount) AS TotalSales,
        COUNT(DISTINCT CustomerID) AS ActiveCustomers
    FROM Sales.OrderHeader
    WHERE OrderDate >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT 
    Yr, Mo, TotalSales, ActiveCustomers,
    AVG(TotalSales) OVER (ORDER BY Yr, Mo ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Avg3MonthSales
FROM MonthlySales
ORDER BY Yr, Mo;

結合 CTE、視窗函數、彙總，單查詢產出完整趨勢分析。