sql语句:
select pe.cfitemnumberid as 项目编码,pe.cfitemname as 项目名称,sum(pe.cfpactmoney) as 项目合同额,
(sum(ee.Cfbudgetsummoney)+sum(de.cfafteradjustmoney)) as 成本费用额,
nvl(round(((sum(pe.cfpactmoney)-(sum(ee.Cfbudgetsummoney)+sum(de.cfafteradjustmoney)))/sum(pe.cfpactmoney)),2),0)*100 || '%' as 毛利率
from ct_ifm_itempactentry pe
left join ct_ifm_itempact p on pe.fparentid = p.fid
left join ct_ifm_itemexpensebudget e on pe.cfitemnumberid = e.cfitemcodingid
left join ct_ifm_itemexpensebudgetentry ee on ee.fparentid = e.fid
left join ct_ifm_itemdatumbudget d on pe.cfitemnumberid = d.cfitemcodingid
left join ct_ifm_itemdatumbudgetentry de on d.fid = de.fparentid
group by pe.cfitemnumberid,pe.cfitemname
where userid = ''显示:
项目编码 项目名称 项目合同额 成本费用额 毛利率
xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%我oracle学的不怎么样,我不知道去除掉列表里面的空行记录,我用了having(count(pe.cfitemnumberid)>0)空行是去掉了,但是我就不能用where来限定条件查询了。。希望各位朋友帮忙解决下,非常感谢!!
select pe.cfitemnumberid as 项目编码,pe.cfitemname as 项目名称,sum(pe.cfpactmoney) as 项目合同额,
(sum(ee.Cfbudgetsummoney)+sum(de.cfafteradjustmoney)) as 成本费用额,
nvl(round(((sum(pe.cfpactmoney)-(sum(ee.Cfbudgetsummoney)+sum(de.cfafteradjustmoney)))/sum(pe.cfpactmoney)),2),0)*100 || '%' as 毛利率
from ct_ifm_itempactentry pe
left join ct_ifm_itempact p on pe.fparentid = p.fid
left join ct_ifm_itemexpensebudget e on pe.cfitemnumberid = e.cfitemcodingid
left join ct_ifm_itemexpensebudgetentry ee on ee.fparentid = e.fid
left join ct_ifm_itemdatumbudget d on pe.cfitemnumberid = d.cfitemcodingid
left join ct_ifm_itemdatumbudgetentry de on d.fid = de.fparentid
group by pe.cfitemnumberid,pe.cfitemname
where userid = ''显示:
项目编码 项目名称 项目合同额 成本费用额 毛利率
xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%
xxx xxx xx xx xx.xx%我oracle学的不怎么样,我不知道去除掉列表里面的空行记录,我用了having(count(pe.cfitemnumberid)>0)空行是去掉了,但是我就不能用where来限定条件查询了。。希望各位朋友帮忙解决下,非常感谢!!
解决方案 »
- 在PL/SQL Developer中数据修改过程中出现问题—— ora-20595, ora-06512, ora-04088
- oracle 遍历数组
- 急需回答
- SQL语句中hr.的用法
- 求一sql语句,请各位大侠帮忙!
- oracal中文问题
- 关于oracle的进程数和光标数的问题,都要疯了,大侠们救救我
- 在Oracle 8i 如何设置监听Listener! version:Release 8.0.5 for Windows NT
- 请问oracle里面,怎么把一个表的blob字段插入到另外一个表的blob中?
- 哪位大侠能提供Oracle 10.2.0.3或10.2.0.4的下载地址,帮小弟解燃眉之急,跪谢!
- 帮忙写一个sql语句
- 视图性能问题
select * from ....join on ... where .... group by
2、如果要在group by之后过滤,可以使用
select * from ....join on ... where .... group by....having
3、group by之后过滤,还可使用嵌套子查询
select * from
(select * from ....join on ... where .... group by....having )
where ............