请大家帮忙解一个sql语句,谢谢(100)

oracel 表a,b,ca表(物品基本信息表): 代码,名称,规格,单价 b表(消耗表a): 代码,日期,次数,......c表(消耗表b): 代码,日期,次数,......我写的统计语句:sql-1: select a.名称,sum(b.次数) as S1 from a, b where (a.代码=b.代码)and(日期='2009-01-01');sql-2: select a.名称,sum(c.次数) as S2 from a, c where (a.代码=c.代码)and(日期>='2000-01-01');我想用一句sql统计语句显示成如下结果,做得到吗?名称 s1 s2某 0 1某某 10 20请大家一定帮帮忙,万分感谢!!

Select 名称, Sum(S1) S1, Sum(S2) S2From (select a.名称,sum(b.次数) as S1, 0 as S2 from a, b where (a.代码=b.代码)and(日期='2009-01-01') group by a.名称 union all select a.名称, 0 as S1, sum(c.次数) as S2 from a, c where (a.代码=c.代码)and(日期>='2000-01-01') group by a.名称 ) TGroup By 名称---------------------如果 日期 不在A表中：Select a.名称, (Select sum(b.次数) from b where (a.代码=b.代码)and(b.日期='2009-01-01') ) as S1, (Select sum(c.次数) from C where (a.代码=C.代码)and(C.日期>='2000-01-01') ) as S2From A

楼上正解~~

多个数量统计呢。哈哈。s1 s2

select a.名称，bb.s1,cc.s2 from (select b.代码,sum(b.次数) as S1 from b where (日期='2009-01-01')) bb,( select c.代码,sum(c.次数) as S2 from c where (日期>='2000-01-01') cc,awhere a.代码=b.代码(+) and a.代码=c.代码(+)

select a.名称,S1=(select sum(b.次数) from b where (a.代码=b.代码)and(b.日期='2009-01-01') ),s2=(Select sum(c.次数) from C where (a.代码=C.代码)and(C.日期>='2000-01-01') ) from a用临时表通过update效率最高。

多人接受答案了。