DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--汇总显示
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
问题:SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
这段代码在oracle出现语法错误,请大侠指点,帮我改一下,能在oracle下运行吗,谢谢
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--汇总显示
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
问题:SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
这段代码在oracle出现语法错误,请大侠指点,帮我改一下,能在oracle下运行吗,谢谢
----代码
select
decode(grouping(Groups)+grouping(Item)+grouping(Color),3,'总计',Groups) Groups,
decode(grouping(Groups)+grouping(Item),1,Groups||'小计',Item) Item,
decode(grouping(Groups)+grouping(Item)+grouping(Color),1,Item||'小计',Color) Color,
sum(Quantity)
from tb
group by rollup(Groups,Item,Color) --运行
SQL>
SQL> with tb as(
2 SELECT 'aa' Groups,'Table' Item,'Blue' Color, 124 Quantity from dual
3 UNION ALL
4 SELECT 'bb','Table','Red', -23 from dual
5 UNION ALL
6 SELECT 'bb','Cup' ,'Green',-23 from dual
7 UNION ALL
8 SELECT 'aa','Chair','Blue', 101 from dual
9 UNION ALL
10 SELECT 'aa','Chair','Red', -90 from dual)
11 select
12 decode(grouping(Groups)+grouping(Item)+grouping(Color),3,'总计',Groups) Groups,
13 decode(grouping(Groups)+grouping(Item),1,Groups||'小计',Item) Item,
14 decode(grouping(Groups)+grouping(Item)+grouping(Color),1,Item||'小计',Color) Color,
15 sum(Quantity)
16 from tb
17 group by rollup(Groups,Item,Color)
18 /
GROUPS ITEM COLOR SUM(QUANTITY)
------ ------ --------- -------------
aa Chair Red -90
aa Chair Blue 101
aa Chair Chair小计 11
aa Table Blue 124
aa Table Table小计 124
aa aa小计 135
bb Cup Green -23
bb Cup Cup小计 -23
bb Table Red -23
bb Table Table小计 -23
bb bb小计 -46
总计 89
12 rows selected