跟我學 MS SQL Server（二）：SELECT 進階選項 — 排序、去重、彙總與分組完全指南

一、SELECT 子句與選項架構

SELECT [ALL | DISTINCT] 
FROM 
[WHERE ]
[GROUP BY  [, ...n]]
[HAVING ]
[ORDER BY  [ASC | DESC] [, ...n]]

二、ORDER BY：多層次排序規則

基本語法

-- 單欄位升序 (預設)
SELECT * FROM Contacts ORDER BY FirstName;

-- 多欄位：優先級由左至右遞減
SELECT * FROM Contacts ORDER BY Company, LastName, FirstName;

-- 混合排序：降序 + 升序
SELECT * FROM Sales ORDER BY NetAmount DESC, LastName ASC, FirstName ASC;

進階技巧

• 排序欄位不必在 SELECT 列表：`SELECT Company FROM Sales ORDER BY StartDate` — 合法且常用
• 運算式排序：`ORDER BY YEAR(OrderDate), MONTH(OrderDate)`
• NULL 排序：`ORDER BY Col ASC` → NULL 在前；`DESC` → NULL 在後 (可用 `ORDER BY CASE WHEN Col IS NULL THEN 1 ELSE 0 END, Col` 控制)
• 動態排序：`ORDER BY CASE @SortCol WHEN 'Name' THEN Name WHEN 'Date' THEN CAST(OrderDate AS VARCHAR(20)) END`
• 分頁 (2012+)：`ORDER BY Id OFFSET @PageSize * (@PageNum-1) ROWS FETCH NEXT @PageSize ROWS ONLY`

三、DISTINCT：去重邏輯與陷阱

核心規則

• 適用於**整個 SELECT 列表**，非單一欄位
• 比較所有輸出欄位值，**完全相同才視為重複**
• 執行順序：`WHERE` → `GROUP BY` → `HAVING` → `SELECT DISTINCT` → `ORDER BY`

範例與陷阱

-- 僅回傳不重複公司名
SELECT DISTINCT Company FROM Sales;

-- 公司+姓名組合去重 (同一公司不同人不重複)
SELECT DISTINCT Company, LastName, FirstName FROM Sales;

-- 陷阱：DISTINCT + ORDER BY 不在 SELECT 列表
SELECT DISTINCT Company FROM Sales ORDER BY StartDate; -- 合法但語義模糊

-- 陷阱：大型資料集 DISTINCT 極慢 (需排序/雜湊)
-- 改用 GROUP BY (可利用索引) 或應用層去重

四、彙總函數：COUNT、SUM、AVG、MIN、MAX

關鍵行為

• 彙總函數**自動忽略 NULL** (除 `COUNT(*)`)
• `SUM`/`AVG` 字串型別隱式轉換 → 錯誤，需 `CAST`
• `AVG` 整數欄位回傳整數 (截斷)，建議 `AVG(CAST(Col AS DECIMAL(10,2)))`
• `MIN`/`MAX` 適用於所有可比較型別 (含字串、日期)

五、GROUP BY：分組邏輯與進階用法

標準分組

SELECT Company, COUNT(*) AS OrderCount, SUM(NetAmount) AS TotalSales
FROM Sales
WHERE OrderDate >= '2023-01-01'
GROUP BY Company
HAVING SUM(NetAmount) > 10000
ORDER BY TotalSales DESC;

進階分組 (2008+)

-- ROLLUP：層級小計 (Company → 總計)
SELECT Company, YEAR(OrderDate) AS Yr, SUM(NetAmount) AS Total
FROM Sales
GROUP BY ROLLUP (Company, YEAR(OrderDate));

-- CUBE：所有維度組合小計
SELECT Company, Region, SUM(NetAmount) AS Total
FROM Sales
GROUP BY CUBE (Company, Region);

-- GROUPING SETS：自定義組合
SELECT Company, Region, SUM(NetAmount) AS Total
FROM Sales
GROUP BY GROUPING SETS ((Company, Region), (Company), (Region), ());

GROUPING 函數識別小計列

SELECT
    CASE WHEN GROUPING(Company) = 1 THEN 'ALL' ELSE Company END AS Company,
    CASE WHEN GROUPING(Region) = 1 THEN 'ALL' ELSE Region END AS Region,
    SUM(NetAmount) AS Total
FROM Sales
GROUP BY CUBE (Company, Region);

六、HAVING vs WHERE：篩選時機差異

-- 正確：WHERE 篩選原始列，HAVING 篩選群組
SELECT Company, COUNT(*) AS Cnt
FROM Sales
WHERE OrderDate >= '2023-01-01'  -- 列級篩選
GROUP BY Company
HAVING COUNT(*) > 100;           -- 群組級篩選

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

WITH MonthlySales AS (
    SELECT
        Company,
        YEAR(OrderDate) AS SalesYear,
        MONTH(OrderDate) AS SalesMonth,
        SUM(NetAmount) AS MonthlyTotal,
        COUNT(*) AS OrderCount
    FROM Sales
    WHERE OrderDate >= DATEADD(YEAR, -2, GETDATE())
    GROUP BY Company, YEAR(OrderDate), MONTH(OrderDate)
),
Ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Company ORDER BY SalesYear DESC, SalesMonth DESC) AS RN
    FROM MonthlySales
)
SELECT Company, SalesYear, SalesMonth, MonthlyTotal, OrderCount
FROM Ranked
WHERE RN <= 12  -- 每公司最近 12 個月
ORDER BY Company, SalesYear DESC, SalesMonth DESC;

八、常見錯誤修正速查

下一篇將講解 SQL 基礎入門：資料庫/資料表/欄位/列/欄位/結果集概念、四大基礎查詢 `SELECT/INSERT/DELETE/UPDATE`、WHERE 條件運算符、字串/日期/數值字面值語法。