GROUP BY 不僅是「分組聚合」,配合 CUBE、ROLLUP、GROUPING SETS、GROUPING_ID,能在單查詢中產出**多維度彙總報表**,避免多次掃描表或應用層二次計算。本文以實戰範例系統講解。
CREATE TABLE SalesInfo (
City NVARCHAR(50),
OrderDate DATE,
OrderID INT
);
INSERT INTO SalesInfo VALUES
(N'北京','2014-06-09',1001),(N'北京','2014-08-09',1002),(N'北京','2013-10-09',1009),
(N'大連','2013-08-09',4001),(N'大連','2013-10-09',4002),(N'大連','2013-05-12',4003),
(N'大連','2014-11-11',4004),(N'大連','2014-12-11',4005);
SELECT City, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City;
/* 結果:
City OrderCount
北京 3
大連 5 */
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH CUBE;
/* 結果包含:
1. (City, Year) 明細組合
2. (City, NULL) → 各城市總計
3. (NULL, Year) → 各年份總計
4. (NULL, NULL) → 總計 */
適用場景: OLAP 多維分析、動態儀表板下鑽、需同時查看行/列/總計的報表。
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH ROLLUP;
/* 結果層級:
1. (City, Year) 明細
2. (City, NULL) → 各城市小計 (Year 彙總)
3. (NULL, NULL) → 總計 (City 彙總)
注意:不產生 (NULL, Year) 跨城市年份總計 */
差異: CUBE = 全排列組合 (2^N 組);ROLLUP = 層級巢狀 (N+1 組)、**順序敏感** (第一欄為最高層)。
-- 僅需:城市×年份明細 + 城市總計 + 總計 (無年份總計)
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY GROUPING SETS ((City, YEAR(OrderDate)), (City), ());
-- 等價於 ROLLUP 但語義更清晰、可自訂任意組合
-- 例:只要城市總計和年份總計,不要明細
GROUP BY GROUPING SETS ((City), (YEAR(OrderDate)), ());
優勢: 效能優於 CUBE/ROLLUP (僅計算需要的組合)、語意顯式、易維護。
結果集中無法直接區分「明細行」與「彙總行」,需輔助函數:
SELECT
City,
YEAR(OrderDate) AS OrderYear,
COUNT(OrderID) AS OrderCount,
GROUPING_ID(City, YEAR(OrderDate)) AS GrpLevel, -- 位元遮罩: 01=Year彙總, 10=City彙總, 11=總計
GROUPING(City) AS IsCityTotal, -- 1=此行為City彙總
GROUPING(YEAR(OrderDate)) AS IsYearTotal,
CASE
WHEN GROUPING_ID(City, YEAR(OrderDate)) = 3 THEN N'總計'
WHEN GROUPING(City) = 1 THEN City + N' 小計'
WHEN GROUPING(YEAR(OrderDate)) = 1 THEN CAST(YEAR(OrderDate) AS NVARCHAR) + N' 年總計'
ELSE City + N' ' + CAST(YEAR(OrderDate) AS NVARCHAR)
END AS Label
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH CUBE
ORDER BY GrpLevel, City, OrderYear;
GROUPING_ID 返回整數位元遮罩,第 i 位對應第 i 個 GROUP BY 欄位,1 表示該欄位已彙總 (NULL)。
-- 僅顯示大連,但北京仍出現 (Count=0)
SELECT City, COUNT(OrderID) FROM SalesInfo WHERE City = N'大連' GROUP BY ALL City;
-- ⚠️ 不支援 CUBE/ROLLUP/HAVING,2012+ 棄用,請用 LEFT JOIN 實現
-- 明細 + 總計 (兩個結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo COMPUTE COUNT(OrderID);
-- 分組明細 + 各組小計 (多結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo ORDER BY City COMPUTE COUNT(OrderID) BY City;
現代替代:GROUP BY ROLLUP + GROUPING_ID 單一結果集,或應用層處理。
DECLARE @GroupBy NVARCHAR(MAX) = N'City, YEAR(OrderDate)'; -- 可從前端傳入
DECLARE @Sql NVARCHAR(MAX) = N'
SELECT
City,
YEAR(OrderDate) AS OrderYear,
COUNT(OrderID) AS OrderCount,
SUM(Amount) AS TotalAmount,
GROUPING_ID(City, YEAR(OrderDate)) AS GrpLevel
FROM SalesInfo
GROUP BY GROUPING SETS ((' + @GroupBy + N'), (City), ())
ORDER BY GrpLevel, City, OrderYear;';
EXEC sp_executesql @Sql;
配合前端下拉選單動態切換彙總維度,單一查詢滿足多種報表視圖。
CREATE INDEX IX_Sales_City_Year ON SalesInfo(City, OrderDate) INCLUDE (OrderID, Amount);GROUPING SETS 精確指定。MAXDOP 控制並行,避免大彙總佔用所有 CPU。GROUP BY 進階語法是關係型資料庫內建 OLAP 能力的體現。 掌握 CUBE (全維度)、ROLLUP (層級彙總)、GROUPING SETS (自訂組合)、GROUPING_ID (行類型識別),能用純 SQL 產出豐富報表數據,減少數據傳輸與應用層計算。新專案**優先使用 GROUPING SETS**,語意清晰且效能可控;維護舊系統時理解 COMPUTE/ALL 即可。