群里提问（二） 关于多层级数据汇总

 

解决方案：

create table t_20120512_a (
id varchar2(6) primary key,
cost number(3),
p2 number(3),
p3 number(3)
)
/
create table t_20120512_b
(
id varchar2(6) primary key,
name varchar2(50),
b varchar2(100)
)
/
insert into t_20120512_a values ('100',1,1,1);
insert into t_20120512_a values ('101001',2,2,2);
insert into t_20120512_a values ('101002',3,3,3);
commit;
insert into t_20120512_b values ('100','语文',null);
insert into t_20120512_b values ('101','物理',null);
insert into t_20120512_b values ('101001','电学',null);
insert into t_20120512_b values ('101002','力学',null);
commit;

select a.id, b.name ,a.total from
(
select distinct case when GROUPING(substr(id,1,3))=0 then substr(id,1,3) else id end id,sum(cost) total from t_20120512_a group by grouping SETS (id,substr(id,1,3))
) a,
t_20120512_b b 
where a.id=b.id;