----------------------------------------
- rollup：对group进行小计
例如
数据：
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
语句：
SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr
FROM Sales
GROUP BY Yr
得到结果，注意没有总计：
Yr Sales ----------- --------------------- 2005 27000.00 2006 44000.00 2007 49000.00
若要得到总计，则：
SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr
UNION ALL
SELECT NULL, SUM(Sales) AS Sales
FROM Sales
FROM Sales
GROUP BY Yr
UNION ALL
SELECT NULL, SUM(Sales) AS Sales
FROM Sales
结果
Yr Sales ----------- --------------------- 2005 27000.00 2006 44000.00 2007 49000.00 NULL 120000.00
但上面效率不高，sales表会扫描2次;
用WITH ROLLUP，会方便得得到上面的数据：
SELECT
CASE WHEN GROUPING(Yr) = 0
THEN CAST (Yr AS CHAR(5))
ELSE 'ALL'
END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP
Yr Sales ----- --------------------- 2005 27000.00 2006 44000.00 2007 49000.00 ALL 120000.00
进一步，对每个雇员进行小结：
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 1 2006 18000.00 1 2007 25000.00 1 NULL 55000.00 2 2005 15000.00 2 2006 6000.00 2 NULL 21000.00 3 2006 20000.00 3 2007 24000.00 3 NULL 44000.00 NULL NULL 120000.00
总结起来，若将上面的结果行列转置：
- cube
但，若需要按Yr统计，得到下面的数据， 则需要用到cube
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
得到结果
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 1 2006 18000.00 1 2007 25000.00 1 NULL 55000.00 2 2005 15000.00 2 2006 6000.00 2 NULL 21000.00 3 2006 20000.00 3 2007 24000.00 3 NULL 44000.00 NULL NULL 120000.00 NULL 2005 27000.00 NULL 2006 44000.00 NULL 2007 49000.00
下面sql语句将上面结果pivot，
SELECT EmpId, [2005], [2006], [2007], [ALL]
FROM
(
SELECT
CASE WHEN GROUPING(EmpId) = 0
THEN CAST (EmpId AS CHAR(7))
ELSE 'ALL'
END AS EmpId,
CASE WHEN GROUPING(Yr) = 0
THEN CAST (Yr AS CHAR(7))
ELSE 'ALL'
END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p
FROM
(
SELECT
CASE WHEN GROUPING(EmpId) = 0
THEN CAST (EmpId AS CHAR(7))
ELSE 'ALL'
END AS EmpId,
CASE WHEN GROUPING(Yr) = 0
THEN CAST (Yr AS CHAR(7))
ELSE 'ALL'
END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p
EmpId 2005 2006 2007 ALL ------- --------------------- --------------------- --------------------- --------------------- 1 12000.00 18000.00 25000.00 55000.00 2 15000.00 6000.00 NULL 21000.00 3 NULL 20000.00 24000.00 44000.00 ALL 27000.00 44000.00 49000.00 120000.00
GROUPSET
http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
rollup和cub会统计所有维度，groupset用来指定维度
