select cbm,cmc,jgxmbh,jgxmmc,jgnd,jhztz,dnjhtz,dnsjtz,
sum(ljtz) over () as ljtz,
sum(ljbfb) over () as ljbfb
from (select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,
sum(jgmng.DNSJTZ) as ljtz,
sum(jgmng.DNSJTZ)/jgmng.JHZTZ as ljzbfb
from v_jgzd_ndss_dvc jgmng
group by jgmng.cbm,jgmng.cmc,jgmng.jgnd,jgmng.jgxmbh,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz)
sum(ljtz) over () as ljtz,
sum(ljbfb) over () as ljbfb
from (select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,
sum(jgmng.DNSJTZ) as ljtz,
sum(jgmng.DNSJTZ)/jgmng.JHZTZ as ljzbfb
from v_jgzd_ndss_dvc jgmng
group by jgmng.cbm,jgmng.cmc,jgmng.jgnd,jgmng.jgxmbh,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz)
cbm cmc jgxmbh jgxmmc jgnd jhztz dnjhtz dnsjtz ljtz ljbfb
01 一厂 2003001 Prj_1st 2003 100 50 50 150 1.5
01 一厂 2003001 Prj_1st 2004 100 50 50 150 1.5
01 一厂 2003001 Prj_1st 2005 100 50 50 150 1.5这个问题怎样决呢????????
sum(ljtz) over (order by cbm rows unbound preceding) as ljtz,
sum(ljbfb) over (order by cbm rows unbound preceding) as ljbfb
from (select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,
sum(jgmng.DNSJTZ) as ljtz,
sum(jgmng.DNSJTZ)/jgmng.JHZTZ as ljzbfb
from v_jgzd_ndss_dvc jgmng
group by jgmng.cbm,jgmng.cmc,jgmng.jgnd,jgmng.jgxmbh,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz)
sum(ljtz) over (order by cbm rows * unbound preceding) as ljtz
执行时光标停留在*处:ORA-00904 无效列名; 如果将rows unbound preceding去掉,则结果同 2003-10-06 17:53:00 我(cocainy)的回复结果
另外,我将各个字段的意思表述如下:
cbm cmc jgxmbh jgxmmc jgnd jhztz dnjhtz dnsjtz ljtz ljbfb
厂编码 厂名称 技改项目编号 ..名称 .年度 计划总投资 当年计划. .实际. 累计投资 累计百分比
select cbm,cmc,jgxmbh,jgxmmc,jgnd,jhztz,dnjhtz,dnsjtz,
sum(ljtz) over (order by cbm rows unbounded preceding) as ljtz,
sum(ljbfb) over (order by cbm rows unbounded preceding) as ljbfb
from (select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,
sum(jgmng.DNSJTZ) as ljtz,
sum(jgmng.DNSJTZ)/jgmng.JHZTZ as ljzbfb
from v_jgzd_ndss_dvc jgmng
group by jgmng.cbm,jgmng.cmc,jgmng.jgnd,jgmng.jgxmbh,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz)
select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,sum(ljtz) over (order by rownum) as ljtz,
sum(ljtz) over (order by rownum)/jhztz ljbfb
from v_jgzd_ndss_dvc jgmng
希望得到:
cbm cmc jgxmbh jgxmmc jgnd jhztz dnjhtz dnsjtz ljtz ljbfb
01 一厂 2003001 Prj_1st 2003 100 50 50 50 0.5
01 一厂 2003001 Prj_1st 2004 100 50 50 100 1.0
01 一厂 2003001 Prj_1st 2005 100 50 50 150 1.501 一厂 2003002 Prj_2nd 2003 200 50 0
01 一厂 2003002 Prj_2nd 2004 200 150 002 二厂 2002001 Prj_1st 2003 200 100 100 100 0.5
02 二厂 2002001 Prj_1st 2004 200 100 100 200 1.0TO: tsj68(tsj)
按照你的做法:得到
cbm cmc jgxmbh jgxmmc jgnd jhztz dnjhtz dnsjtz ljtz ljbfb
01 一厂 2003001 Prj_1st 2003 100 50 50 50 0.5
01 一厂 2003001 Prj_1st 2004 100 50 50 100 1.0
01 一厂 2003001 Prj_1st 2005 100 50 50 150 1.501 一厂 2003002 Prj_2nd 2003 200 50 150 1.5
01 一厂 2003002 Prj_2nd 2004 200 150 150 1.502 二厂 2002001 Prj_1st 2003 200 100 100 250 2.5
02 二厂 2002001 Prj_1st 2004 200 100 100 350 3.5TO: beckhambobo(beckham)
按照你的做法得到:
cbm cmc jgxmbh jgxmmc jgnd jhztz dnjhtz dnsjtz ljtz ljbfb
01 一厂 2003001 Prj_1st 2003 100 50 50 50 0.5
01 一厂 2003001 Prj_1st 2004 100 50 50 100 1.0
01 一厂 2003001 Prj_1st 2005 100 50 50 150 1.501 一厂 2003002 Prj_2nd 2003 200 50 150 0.75
01 一厂 2003002 Prj_2nd 2004 200 150 150 1.002 二厂 2002001 Prj_1st 2003 200 100 100 250 1.25
02 二厂 2002001 Prj_1st 2004 200 100 100 350 1.40问题仍然存在, 所以还要麻烦两位!
select cbm,cmc,jgxmbh,jgxmmc,jgnd,jhztz,dnjhtz,dnsjtz,
sum(ljtz) over (partition by cbm,cmc,jgxmbh,jgxmmc order by cbm rows unbounded preceding) as ljtz,
sum(ljbfb) over (partition by cbm,cmc,jgxmbh,jgxmmc order by cbm rows unbounded preceding) as ljbfb
from (select jgmng.cbm,jgmng.cmc,jgmng.jgxmbh,jgmng.jgxmmc,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz,
sum(jgmng.DNSJTZ) as ljtz,
sum(jgmng.DNSJTZ)/jgmng.JHZTZ as ljzbfb
from v_jgzd_ndss_dvc jgmng
group by jgmng.cbm,jgmng.cmc,jgmng.jgnd,jgmng.jgxmbh,
jgmng.jgnd,jgmng.jhztz,jgmng.dnjhtz,jgmng.dnsjtz)
这二十分,多给你一点