现在我有这样一个表:
select *
from (
select 'A' as cla,'12't1,'23't2 from dual
union
select 'A' as cla,'22't1,'55't2 from dual
union
select 'B' as cla,'13't1,'25't2 from dual
union
select 'B' as cla,'62't1,'57't2 from dual
union
select 'C' as cla,'18't1,'28't2 from dual
union
select 'C' as cla,'26't1,'85't2 from dual
) 想得到如下数据
CLA T1 T2
A 12 23
A 22 55
合计 34 78
B 13 25
B 62 57
合计 75 82
C 18 28
C 26 85
合计 44 113请问如何实现?
select *
from (
select 'A' as cla,'12't1,'23't2 from dual
union
select 'A' as cla,'22't1,'55't2 from dual
union
select 'B' as cla,'13't1,'25't2 from dual
union
select 'B' as cla,'62't1,'57't2 from dual
union
select 'C' as cla,'18't1,'28't2 from dual
union
select 'C' as cla,'26't1,'85't2 from dual
) 想得到如下数据
CLA T1 T2
A 12 23
A 22 55
合计 34 78
B 13 25
B 62 57
合计 75 82
C 18 28
C 26 85
合计 44 113请问如何实现?
2 select id,num1,num2 from test
3 union
4 select decode(id,'','','合计'),sum(num1),sum(num2) from test group by rollup(id))
5 where id is not null;ID NUM1 NUM2
---- ---------- ----------
A 12 23
A 23 33
B 33 54
B 43 55
C 32 90
合计 32 90
合计 35 56
合计 76 1098 rows selected.SQL> select * from test;I NUM1 NUM2
- ---------- ----------
A 12 23
A 23 33
B 33 54
B 43 55
C 32 90
select id,num1,num2 from test group by id
union
select decode(id,'','','合计'),sum(num1),sum(num2) from test group by id)
where id is not null;
select decode(grouping(t1),1,'合计',cla),sum(t1),sum(t2) from tab1
group by rollup(cla,t1)
2 sum(t1),
3 sum(t2)
4 from t
5 group by rollup(cla, rownum)
6 having grouping_id(cla, rownum) < 2
7 /CLA1 SUM(T1) SUM(T2)
---- ---------- ----------
A 12 23
A 22 55
合计 34 78
B 13 25
B 62 57
合计 75 82
C 18 28
C 26 85
合计 44 1139 rows selected