作者：双鱼獒主 | 来源：互联网 | 2014-07-13 17:52
解决方案：createtablet_20120512_a(idvarchar2(6)primarykey,costnumber(3),www.2cto.comp2number(3),p3number(3))/createtablet_20120512_b(idvarchar2(6)primarykey,namev...
解决方案：
create table t_20120512_a (
id varchar2(6) primary key,
cost number(3), www.2cto.com
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 (&＃39;100&＃39;,1,1,1);
insert into t_20120512_a values (&＃39;101001&＃39;,2,2,2);
insert into t_20120512_a values (&＃39;101002&＃39;,3,3,3);
commit; www.2cto.com
insert into t_20120512_b values (&＃39;100&＃39;,&＃39;语文&＃39;,null);
insert into t_20120512_b values (&＃39;101&＃39;,&＃39;物理&＃39;,null);
insert into t_20120512_b values (&＃39;101001&＃39;,&＃39;电学&＃39;,null);
insert into t_20120512_b values (&＃39;101002&＃39;,&＃39;力学&＃39;,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;
作者 蓝红石
京公网安备 11010802041100号