sql 中group by with rollup的用法

CREATE TABLE #test (
Name VARCHAR(10),
[procedure] CHAR(1),
model VARCHAR(5),
quantity INT
);
INSERT INTO #test
SELECT 'A','1','φ50',500 UNION ALL
SELECT 'A','1','φ50',600 UNION ALL
SELECT 'A','1','φ100',500 UNION ALL
SELECT 'A','2','φ50',700 UNION ALL
SELECT 'A','2','φ100',200 UNION ALL
SELECT 'B','1','φ50',1000;
SELECT
CASE
WHEN GROUPING(Name) = 1 THEN '总计'
WHEN GROUPING(Name) = 0 AND GROUPING([procedure]) = 1 THEN Name + '合计'
WHEN GROUPING(Name) = 0 AND GROUPING([procedure]) = 0 AND GROUPING([model]) = 1 THEN Name + '的' + [procedure] + '小计'
ELSE Name
END AS Name,
CASE
WHEN GROUPING([model]) = 1 THEN ''
ELSE [procedure]
END AS [procedure],
ISNULL(model,'') AS model,
SUM(quantity) AS quantity
FROM
#test
group by
Name,
[procedure],
model with rollup;
Name procedure model quantity
----------------- --------- ----- -----------
A 1 φ100 500
A 1 φ50 1100
A的1小计 1600
A 2 φ100 200
A 2 φ50 700
A的2小计 900
A合计 2500
B 1 φ50 1000
B的1小计 1000
B合计 1000
总计 3500
(11 行受影响)
SQL Server 2008 Express 下测试通过.