SQL 排序、分組、統計 10 大進階技巧：GROUP BY、ROLLUP、CUBE、GROUPING 完整實戰

1. ORDER BY — 排序 ≠ 分組

僅改變輸出順序，不合併行。SELECT ZIP FROM Customers ORDER BY ZIP

2. GROUP BY — 真正分組，去重

SELECT ZIP FROM Customers GROUP BY ZIP;  -- 唯一 ZIP 清單

• SELECT 清單 = GROUP BY 清單 + 聚合函數
• 不保證輸出順序，需加 ORDER BY
• 不可引用別名

3. WHERE 先於 GROUP BY 過濾

SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ZIP;

WHERE 在分組前過濾行，不支援聚合函數。

4. GROUP BY ALL — 保留所有分組 (含空組)

SELECT ZIP, COUNT(ZIP) AS KY_Count 
FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP;

非 KY 的 ZIP 也會出現，聚合值為 0。遠端查詢不支援。

5. HAVING — 分組後過濾

SELECT ZIP, COUNT(*) AS Cnt FROM Customers GROUP BY ZIP HAVING COUNT(*) = 1;

只保留單一客戶的 ZIP。口訣：WHERE 過濾行，HAVING 過濾組。

6. WHERE vs HAVING 執行順序

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

7. 聚合函數統計分組

SELECT OrderID, SUM(Cost * Quantity) AS OrderTotal 
FROM Orders GROUP BY OrderID;

SELECT 可含聚合，GROUP BY 不可。

8. ROLLUP — 階層式小計 + 總計

SELECT Customer, OrderNumber, SUM(Cost * Quantity) AS OrderTotal
FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP;

輸出：各訂單小計 → 各客戶小計 → 總計 (第一行)。
限制：不支援 DISTINCT、GROUP BY ALL。

9. CUBE — 全維度交叉統計

SELECT Customer, OrderNumber, SUM(Cost * Quantity) AS OrderTotal
FROM Orders GROUP BY Customer, OrderNumber WITH CUBE;

在 ROLLUP 基礎上，增加 每個客戶的總計行 (交叉表)。
最全統計，但行數 = (n+1)×(m+1)，大維度慎用。

10. GROUPING() — 識別小計/總計行

SELECT GROUPING(Customer) AS IsCustTotal, OrderNumber, SUM(Cost * Quantity) AS OrderTotal
FROM Orders GROUP BY Customer, OrderNumber WITH CUBE;

• 返回 1 = 該欄為小計/總計行 (由 ROLLUP/CUBE 產生)
• 返回 0 = 明細行 (原始 GROUP BY 產生)
• 常用於報表顯示「總計」文字替代 NULL

實戰範例：多維銷售報表

SELECT 
    CASE WHEN GROUPING(Region) = 1 THEN '全區' ELSE Region END AS Region,
    CASE WHEN GROUPING(Category) = 1 THEN '全類' ELSE Category END AS Category,
    SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Region, Category WITH CUBE
ORDER BY GROUPING(Region), Region, GROUPING(Category), Category;

輸出：明細 → 類別小計 → 區域小計 → 總計，完美支援報表呈現。