存在这样一个表的数据:
数1 数2 数3
行1: 1 10 20
行2: 2 20 10
行3: 4 5 10
行4: 5 10 10
现在oracle中使用SQL语句实现如下效果:
数1 数2 数3
合计:12 45 50
行1: 1 10 20
行2: 2 20 10
行3: 4 5 10
行4: 5 10 10
大家有没有什么好思路,提供一下,最好能把SQL写出来。呵呵 多谢了。
数1 数2 数3
行1: 1 10 20
行2: 2 20 10
行3: 4 5 10
行4: 5 10 10
现在oracle中使用SQL语句实现如下效果:
数1 数2 数3
合计:12 45 50
行1: 1 10 20
行2: 2 20 10
行3: 4 5 10
行4: 5 10 10
大家有没有什么好思路,提供一下,最好能把SQL写出来。呵呵 多谢了。
union all
select col1,col2,col3 from table
---------- ---------- ----------
4 5 10
2 20 10
1 10 20
5 10 10SQL> SELECT SUM(数1) 数1,SUM(数2) 数2,SUM(数3) 数3 FROM test
2 UNION ALL
3 SELECT 数1,数2,数3 FROM test; 数1 数2 数3
---------- ---------- ----------
12 45 50
4 5 10
2 20 10
1 10 20
5 10 10SQL>
(
select 1 r1,1 f1,10 f2,20 f3 from dual
union all
select 2 r1,1 f1,10 f2,20 f3 from dual
union all
select 3 r1,1 f1,10 f2,20 f3 from dual
union all
select 4 r1,1 f1,10 f2,20 f3 from dual
union all
select 5 r1,1 f1,10 f2,20 f3 from dual
)
select decode(grouping(r1),1,'合计',r1) r1,
grouping(r1) g1,
sum(f1) f1,sum(f2) f2,sum(f3) f3
from tmp
group by rollup(r1)
order by g1 desc
union all
select 数1,数2,数3 from 这样一个表
union all
select col1,col2,col3 from table
将3楼的改下
with tmp as
(
select 1 f1,10 f2,20 f3 from dual
union all
select 2 f1,20 f2,10 f3 from dual
union all
select 4 f1,5 f2,10 f3 from dual
union all
select 5 f1,10 f2,10 f3 from dual
)
select decode(grouping(rownum),1,'合计','行'||rownum) " ",
sum(f1) f1,sum(f2) f2,sum(f3) f3
from tmp
group by rollup(rownum)
order by case grouping(rownum) when 1 then 1 else 2 end,rownum
数据量大的话用union all更好些
union all
select 数1,数2,数3 from tb
(
select 1 r1,1 f1,10 f2,20 f3 from dual
union all
select 2 r1,1 f1,10 f2,20 f3 from dual
union all
select 3 r1,1 f1,10 f2,20 f3 from dual
union all
select 4 r1,1 f1,10 f2,20 f3 from dual
union all
select 5 r1,1 f1,10 f2,20 f3 from dual
)
select r1, sum(f1),sum(f2),sum(f3) from tmp
group by rollup(r1,f1,f2,f3)
having grouping_id(r1,f1,f2,f3) in (0,power(2,3)+power(2,2)+power(2,1)+power(2,0))
from test t
union all
select * from test t