sql 中group by with rollup的用法

来源：学生作业帮编辑：神马作文网作业帮分类：英语作业时间：2024/11/12 22:34:12

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

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 下测试通过.

sql 中group by with rollup的用法 SQL 中with的用法 sql中order by和group by的区别 SQL的GROUP BY错误 sql中with as的用法 group by的用法 SQL中group by语句有何作用? 数据库的group by有什么意义呢?(sql server) SQL 中ESCAPE的用法? SQL中like的用法 SQL中 \'group by\'和\'order by \'有什么不同呢 GROUP BY ROLLUP (A,B,C) HAVING A IS NOT NULL AND D NOT NULL是