sql 中group by with rollup的用法有一张表,我想做一个分类小计和总计的栏：Name procedure model quantityA 1 φ50 500A 1 φ50 600A 1 φ100 500A 2 φ50 700A 2 φ100 200B 1 φ50 1000使用了group by Name,procedure,model,quantity with

sql 中group by with rollup的用法有一张表,我想做一个分类小计和总计的栏：Name procedure model quantityA 1 φ50 500A 1 φ50 600A 1 φ100 500A 2 φ50 700A 2 φ100 200B 1 φ50 1000使用了group by Name,procedure,model,quantity with
sql 中group by with rollup的用法
有一张表,我想做一个分类小计和总计的栏：
Name procedure model quantity
A 1 φ50 500
A 1 φ50 600
A 1 φ100 500
A 2 φ50 700
A 2 φ100 200
B 1 φ50 1000
使用了group by Name,procedure,model,quantity with rollup
变成了：
Name procedure model quantity
A 1 φ50 1100
A 1 φ100 500
A 1 NULL 1600
A 2 φ50 700
A 2 φ100 200
A 2 NULL 900
A NULL NULL 2500
B 1 φ50 1000
B 1 NULL 1000
B NULL NULL 1000
NULL NULL NULL 3500
我想把它显示成：
Name procedure model quantity
A 1 φ50 1100
A 1 φ100 500
A的1小计 1600
A 2 φ50 700
A 2 φ100 200
A 的2小计 900
A 合计 2500
B 1 φ50 1000
B的1小计 1000
B合计 1000
总计 3500

sql 中group by with rollup的用法有一张表,我想做一个分类小计和总计的栏：Name procedure model quantityA 1 φ50 500A 1 φ50 600A 1 φ100 500A 2 φ50 700A 2 φ100 200B 1 φ50 1000使用了group by Name,procedure,model,quantity with
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 下测试通过.