SQL Server GROUP BY 進階應用：TOP、CUBE、ROLLUP、GROUPING 函數與 HAVING 篩選

1. TOP N 結合 ORDER BY (非標準分組，但常用)

SELECT TOP 10 orderid, amt = unitprice * quantity
FROM orderdetails
ORDER BY amt DESC;

2. 基礎 GROUP BY

SELECT prod_id, SUM(qty * unit_price) AS tot_amt
FROM sale_item
GROUP BY prod_id
ORDER BY tot_amt DESC;

規則：SELECT 清單中非彙總欄位**必須**出現在 GROUP BY 中。

3. WITH CUBE — 全維度超級彙總

SELECT sup_id, prod_id, SUM(qty*unit_price) AS amt, SUM(qty) AS qty
FROM sale_item
GROUP BY sup_id, prod_id WITH CUBE;

輸出包含：
• 一般群組列
• 每個 sup_id 小計 (prod_id = NULL)
• 每個 prod_id 小計 (sup_id = NULL)
• 總計列 (sup_id=NULL, prod_id=NULL)

4. WITH ROLLUP — 階層式小計 (僅最左欄位層層彙總)

GROUP BY sup_id, prod_id WITH ROLLUP;

輸出：一般群組列 + 每個 sup_id 小計 + 總計。**不產生** prod_id 維度小計。

5. GROUPING() — 識別超級彙總列產生的 NULL

SELECT 
    prod_id, GROUPING(prod_id) AS is_prod_total,
    order_id, GROUPING(order_id) AS is_order_total,
    SUM(quantity) AS total_qty
FROM orders
GROUP BY prod_id, order_id WITH CUBE
ORDER BY prod_id, order_id;

• 回傳 0 = 明細資料列；1 = CUBE/ROLLUP 產生的彙總列
• 搭配 CASE 顯示 'All Products'、'All Orders' 替代 NULL

6. 真實資料 NULL vs 彙總 NULL 的區分

SELECT 
    CASE WHEN GROUPING(prod_id)=1 THEN 'All Products' 
         WHEN prod_id IS NULL THEN 'Unknown Product' 
         ELSE prod_id END AS ProdLabel,
    ...

7. HAVING — 群組層級篩選

-- 找出同一產品有多家供應商 (代碼重複)
SELECT prod_id, COUNT(*) AS sup_cnt
FROM stock
GROUP BY prod_id
HAVING COUNT(*) > 1;

WHERE vs HAVING：WHERE 篩選**明細列** (彙總前)；HAVING 篩選**群組結果** (彙總後)。

現代替代方案：GROUPING SETS (SQL 2008+) 可精確指定需彙總的組合，比 CUBE/ROLLUP 更靈活、效能更好。大型報表建議改用 GROUPING SETS。