SELECT GSBM,
(SELECT sum(b.assvalue) FROM t_jplods_gssb a inner join t_jplods_gssbsub b on a.docid=b.pdocid where year=to_char(sysdate,'yyyy') and month=to_char(sysdate,'mm')-1 and asstype='人工工时' group by GSBM )as bb,
(SELECT sum(b.assvalue) FROM t_jplods_gssb a inner join t_jplods_gssbsub b on a.docid=b.pdocid where year=to_char(sysdate,'yyyy') and month=to_char(sysdate,'mm')-1 and asstype='生产量' group by GSBM) as cc
FROM t_jplods_gssb a inner join t_jplods_gssbsub b on a.docid=b.pdocid where year=to_char(sysdate,'yyyy') and month=to_char(sysdate,'mm')-1 group by GSBM
如果要同时获得两列的商值?
改了之后执行结果为合计数,未按GSBM分类为什么?
追答select dd.gsbm,
(select sum(b.assvalue)
from t_jplods_gssb a
inner join t_jplods_gssbsub b on a.docid = b.pdocid
where year = to_char(sysdate, 'yyyy')
and month = to_char(sysdate, 'mm') - 1
and asstype = '人工工时'
and a.gsbm = dd.gsbm) as bb,
(select sum(b.assvalue)
from t_jplods_gssb a
inner join t_jplods_gssbsub b on a.docid = b.pdocid
where year = to_char(sysdate, 'yyyy')
and month = to_char(sysdate, 'mm') - 1
and asstype = '生产量'
and a.gsbm = dd.gsbm) as cc,
(select sum(b.assvalue)
from t_jplods_gssb a
inner join t_jplods_gssbsub b on a.docid = b.pdocid
where year = to_char(sysdate, 'yyyy')
and month = to_char(sysdate, 'mm') - 1
and asstype = '生产量'
and a.gsbm = dd.gsbm) /
(select sum(b.assvalue)
from t_jplods_gssb a
inner join t_jplods_gssbsub b on a.docid = b.pdocid
where year = to_char(sysdate, 'yyyy')
and month = to_char(sysdate, 'mm') - 1
and asstype = '人工工时'
and a.gsbm = dd.gsbm) "商"
from t_jplods_gssb dd
inner join t_jplods_gssbsub ee on dd.docid = ee.pdocid
where year = to_char(sysdate, 'yyyy')
and month = to_char(sysdate, 'mm') - 1
group by dd.gsbm
这样修改