select bd_glorgbook.glorgbookcode,bd_glorgbook.glorgbookname,       gl_detail.explanation,       bd_accsubj.dispname,       gl_detail.debitamount 借方,       gl_detail.creditamount 贷方,       gl_detail.prepareddatev 制单日期,       gl_voucher.no 凭证号,       wmsys.wm_concat(gl_freevalue.valuecode) valuecode,       wmsys.wm_concat(gl_freevalue.valuename) valuename,       gl_detail.pk_systemv,       gl_detail.detailindex  from bd_accsubj  join gl_detail    on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj  join bd_glorgbook    on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook  join gl_voucher    on gl_detail.pk_voucher = gl_voucher.pk_voucher  left join gl_freevalue    on gl_detail.assid = gl_freevalue.freevalueid where gl_detail.dr = '0' and ( bd_accsubj.subjcode like  '4104010508%'or bd_accsubj.subjcode like  '550212%')       and bd_glorgbook.glorgbookcode like '01%-0001'   and gl_detail.yearv = '2013' /* and gl_detail.creditamount ='0'*/   and gl_detail.explanation<>'期初'   and gl_detail.debitamount<>0  /* and gl_detail.explanation='付财大凤凰楼项目人工费(洪荣森)'*/--and bd_accsubj.subjcode like '150103%' group by bd_glorgbook.glorgbookcode,bd_glorgbook.glorgbookname, gl_detail.explanation,          bd_accsubj.dispname,          gl_detail.creditamount,          gl_detail.debitamount,          gl_detail.prepareddatev,          gl_voucher.no,          gl_detail.pk_systemv,          gl_detail.detailindex order by  bd_glorgbook.glorgbookcode, bd_accsubj.dispname,gl_detail.prepareddatev, gl_voucher.no

 发给建行的

select-- bd_glorgbook.glorgbookcode,--bd_glorgbook.glorgbookname,--gl_detail.prepareddatev 制单日期,    -- gl_voucher.no 凭证号,    -- gl_detail.explanation,     gl_detail.yearv,       bd_accsubj.dispname,       sum(gl_detail.debitamount)   金额      -- gl_detail.creditamount  贷方,       from gl_detail, bd_accsubj, bd_glorgbook, gl_voucher where gl_detail.pk_accsubj = bd_accsubj.pk_accsubj   and gl_detail.pk_glorgbook = bd_glorgbook.pk_glorgbook         and gl_detail.pk_voucher = gl_voucher.pk_voucher   and gl_detail.dr = '0'    and gl_detail.periodv<>'00'    and gl_detail.debitamount<>0   and gl_detail.yearv in ('2011','2012','2013')   --and bd_glorgbook.glorgbookcode = '010201-0001'   --and (gl_voucher.no='263' or gl_voucher.no='207')   and (bd_accsubj.subjcode like '550212%' or bd_accsubj.subjcode like '4104010508%')   and bd_glorgbook.glorgbookcode like '01%'    group by   gl_detail.yearv,bd_accsubj.dispname order by  gl_detail.yearv,bd_accsubj.dispname

下面是rollup安装分公司小计

select  bd_glorgbook.glorgbookcode,           nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习                      bd_accsubj.subjcode,bd_accsubj.subjname,           sum( case when gl_balance.year= '2010' then  gl_balance.debitamount else 0 end) "2010发生额",           sum( case when gl_balance.year= '2011' then  gl_balance.debitamount else 0 end) "2011发生额",           sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) "2012发生额"                            from gl_balance, bd_accsubj, bd_glorgbook    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook       and bd_glorgbook.glorgbookcode like '01%-0001'        and (bd_accsubj.subjcode like '5502%' or bd_accsubj.subjcode like '5503%')       and gl_balance.period<>'00'       and gl_balance.year in('2010','2011','2012')      group by bd_glorgbook.glorgbookcode,  rollup((bd_accsubj.subjcode,bd_accsubj.subjname , bd_glorgbook.glorgbookname))          order by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode

2014.9.10 更新

统计各个公司2010-2014费用

期间发生了问题，sum的时候里面不能是数字和汉字的组合，否则

 

单位取到万

select glorgbookcode,公司名称,( case substr(subjcode,1,4) when '5502' then '管理费用'  when '5503' then '财务费用'    when '5201' then '投资收益'      when '5301' then '营业外收入'        else    '营业外支出' end )"会计科目",round(sum(A)/10000,2)"2010发生额",round(sum(B)/10000,2) "2011发生额",round(sum(C)/10000,2)"2012发生额",round(sum(D)/10000,2) "2013发生额",round(sum(E)/10000,2) "2014发生额" from (  select  bd_glorgbook.glorgbookcode,           nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习                      bd_accsubj.subjcode,bd_accsubj.subjname,           sum( case when gl_balance.year= '2010' then  gl_balance.debitamount else 0 end) A,           sum( case when gl_balance.year= '2011' then  gl_balance.debitamount else 0 end)B,           sum( case when gl_balance.year= '2012' then  gl_balance.debitamount else 0 end) C,             sum( case when gl_balance.year= '2013' then  gl_balance.debitamount else 0 end) D,           sum( case when gl_balance.year= '2014' then  gl_balance.debitamount else 0 end) E                              from gl_balance, bd_accsubj, bd_glorgbook    where gl_balance.pk_accsubj = bd_accsubj.pk_accsubj       and bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbook       and bd_glorgbook.glorgbookcode like '01%-0001'        and (bd_accsubj.subjcode like '5502%' --管理费用       or bd_accsubj.subjcode like '5503%'  -- 财务费用       or bd_accsubj.subjcode like '5201%' --投资收益       or bd_accsubj.subjcode like '5301%' --营业外收入       or bd_accsubj.subjcode like '5601%' --营业外支出       )       and gl_balance.period<>'00'       and gl_balance.year in('2010','2011','2012','2013','2014')      group by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode,bd_accsubj.subjname , bd_glorgbook.glorgbookname          order by bd_glorgbook.glorgbookcode,  bd_accsubj.subjcode)    group by glorgbookcode,公司名称,substr(subjcode,1,4)    order by glorgbookcode,substr(subjcode,1,4)