萬盛學電腦網

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

SQL Server 資料彙總五大招:GROUP BY 進階技巧全解析

範例資料來源

AdventureWorks2012 範例資料庫:`Sales.SalesOrderDetail` (明細) + `Sales.SalesOrderHeader` (主單)。

招式一:單欄位分組彙總

SELECT CarrierTrackingNumber,
       SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail
GROUP BY CarrierTrackingNumber;
  • `GROUP BY` 欄位必須出現在 `SELECT` 列表 (或為彙總函數引數)。
  • `WHERE` 在分組**前**過濾列;`HAVING` 在分組**後**過濾群組。

招式二:多欄位分組 (複合鍵)

SELECT D.ProductID, H.OrderDate, SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY D.ProductID, H.OrderDate
ORDER BY SummarizedLineTotal DESC;
  • 依 `ProductID` + `OrderDate` 唯一組合分群。
  • `ORDER BY` 可使用別名 `SummarizedLineTotal`。

招式三:NULL 值分組行為

SQL Server 視 **所有 NULL 為相等**,歸為同一群組。

CREATE TABLE #NullGroupBy (OrderDate DATE, Amount INT);
INSERT #NullGroupBy VALUES (NULL,100), ('2014-10-30',100), ('2014-10-31',100), (NULL,100);

SELECT OrderDate, SUM(Amount) AS TotalAmount
FROM #NullGroupBy
GROUP BY OrderDate;
/* 結果:
OrderDate   TotalAmount
NULL        200
2014-10-30  100
2014-10-31  100
*/
DROP TABLE #NullGroupBy;

招式四:GROUP BY 運算式 (非直接欄位)

可對欄位套用函數/運算式後再分組,如按 **年月** 彙總:

SELECT CONVERT(CHAR(7), H.OrderDate, 120) AS [YYYY-MM],
       SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY CONVERT(CHAR(7), H.OrderDate, 120)
ORDER BY SummarizedLineTotal DESC;
  • `CONVERT(CHAR(7), date, 120)` 產生 `yyyy-mm` 格式 (ODBC 標準)。
  • 同樣適用:`YEAR(date)`、`MONTH(date)`、`DATEPART(quarter, date)`、案例運算式等。

招式五:HAVING 進階過濾群組

SELECT D.ProductID, H.OrderDate, SUM(LineTotal) AS SummarizedLineTotal
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY D.ProductID, H.OrderDate
HAVING SUM(LineTotal) > 200000
ORDER BY SummarizedLineTotal DESC;
  • 保留彙總金額 > 200,000 的產品-日期組合。
  • `HAVING` 可使用彙總函數、`GROUP BY` 欄位、標量子查詢;**不可**使用未分組、未彙總的欄位。

進階擴充:GROUPING SETS / ROLLUP / CUBE (SQL 2008+)

-- 多層次小計一次查詢
SELECT ProductID, OrderDate, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail D
JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID
GROUP BY GROUPING SETS (
    (ProductID, OrderDate),  -- 明細
    (ProductID),             -- 產品小計
    (OrderDate),             -- 日期小計
    ()                       -- 總計
);

`GROUPING_ID()` 函數可識別各列屬於哪個分組集。

效能提示

  • **索引支援**:`GROUP BY` 欄位建立索引 (含 `JOIN` 鍵) 可避免 `Sort` / `Hash Match` 操作。
  • **預先彙總**:大量歷史資料建立彙總表/索引檢視,即時查詢直接讀取。
  • **資料分割**:大表按日期分割,分區消除加速歷史彙總。
copyright © 萬盛學電腦網 all rights reserved