表table中有字段f1,f2, classid,groupdel
现在的需求是
根据字段classid两个不同的值(例如a和b),按字段groupdel进行分组求和,把f1求和,f2求和,
然后再相加,按字段groupdel进行分组现在写的语句如下:(希望有更好的算法)
select (sum(t1.f1) + sum(t2.f2)) salary ,t1.groupdel from
(select sum(nvl(f1, 0)) f1 ,groupdel
from wa_data a left join bd_psndoc b on a.psnid=b.pk_psndoc where
a.classid='a' group by groupdel ) t1
inner join
(select sum(nvl(f2, 0)) f2, groupdel
from wa_data a left join bd_psndoc b on a.psnid=b.pk_psndoc where
a.classid='b' group by groupdel) t2
on t1.groupdel = t2.groupdel
group by t1.groupdel
order by t1.groupdel
现在的需求是
根据字段classid两个不同的值(例如a和b),按字段groupdel进行分组求和,把f1求和,f2求和,
然后再相加,按字段groupdel进行分组现在写的语句如下:(希望有更好的算法)
select (sum(t1.f1) + sum(t2.f2)) salary ,t1.groupdel from
(select sum(nvl(f1, 0)) f1 ,groupdel
from wa_data a left join bd_psndoc b on a.psnid=b.pk_psndoc where
a.classid='a' group by groupdel ) t1
inner join
(select sum(nvl(f2, 0)) f2, groupdel
from wa_data a left join bd_psndoc b on a.psnid=b.pk_psndoc where
a.classid='b' group by groupdel) t2
on t1.groupdel = t2.groupdel
group by t1.groupdel
order by t1.groupdel
解决方案 »
- 困扰我很久的statspack问题
- oracle的distinct问题:为什么按照例子出不来结果??
- 怎么安全删除dbf文件
- 怎么导LONG RAW数据
- 【求助】instead of 触发器无法提交修改 (不好意思,没分了)
- SI OBJECT BROWSER 8的注册机或注册码
- 重金求schema(或称方案)的在不同Oracle实例间复制的解决方法!
- oracle数据库表中字段的导入导出问题
- 请问存储过程中如何调用其他ORACLE用户的对象?
- 紧急求救,删出rooback表空间
- insert into table a(a1,a2) select (b1,b2) from b 的问题
- oracle update 更新多字段用一条sql 的预设默认值的问题
(sum(t1.f1) + sum(t2.f2))直接sum(f1+f2)
你这是两个表吧。wa_data,bd_psndoc
那个inner也可以修改。没明白具体什么意思。直接
select sum(a+b) from .. group by classid,groupdel
字段classid两个不同的值(例如a和b),
select (t1.f1 + t2.f2) salary,
t1.groupdel
from (select sum(nvl(f1, 0)) f1, groupdel
from wa_data a
left join bd_psndoc b on a.psnid = b.pk_psndoc
where a.classid = 'a'
and groupdel is not null
group by groupdel) t1
inner join (select sum(nvl(f2, 0)) f2, groupdel
from wa_data a
left join bd_psndoc b on a.psnid = b.pk_psndoc
where a.classid = 'b'
and groupdel is not null
group by groupdel) t2 on t1.groupdel = t2.groupdel
order by t1.groupdel
再分组统计就是了。那个nvl()不需要用的。直接sum.