数据库withrollup(数据库原理及应用)
SQL中的with rollup是干什么的
用 ROLLUP 汇总数据
在生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。
我现在还没学会用这个。所以只能给你这样的回答
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 下测试通过.
Oracle sql语句加上with rollup 就报错，个人觉得我写的语法没错
你第一个截图那张，with用的是sqlserver的语法，你用到oracle里当然不行
oralce里使用是这样的
select?a.provider,a.materialno,sum(a.quantity)
from?tb?a?group?by?rollup(a.provider,a.materialno)
后一个cmd下的语法，也是sqlserver或者access,或者mysql，总之不是oracle里的语法
oracle下这样
insert?into?tb?
select?'Canon','001',500?from?dual
union?all
select?'Canon','001',200?from?dual
union?all
select?'Canon','002',100?from?dual
union?all
select?'Canon','002',300?from?dual
union?all
select?'Sony','001',200?from?dual
union?all
select?'IBM','002',100?from?dual
union?all
select?'IBM','001',600?from?dual
union?all
select?'IBM','002',200?from?dual;