基础资料
select bd_glorgbook.glorgbookcode,nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习substr(bd_accsubj.subjcode,1,4)科目编码,---1111应收票据（借方），1131应收账款（+贷-借），2123工程结算--以下是贷方sum( case when gl_balance.year= '2012' then gl_balance.debitamount else 0 end) "2012借方发生额",sum( case when gl_balance.year= '2013' then gl_balance.debitamount else 0 end) "2013借方发生额",sum( case when gl_balance.year= '2014' then gl_balance.debitamount else 0 end) "2014借方发生额" , sum( case when gl_balance.year= '2015' then gl_balance.debitamount else 0 end) "2015借方发生额" ,--以下是贷方sum( case when gl_balance.year= '2012' then gl_balance.creditamount else 0 end) "2012贷方发生额",sum( case when gl_balance.year= '2013' then gl_balance.creditamount else 0 end) "2013贷方发生额",sum( case when gl_balance.year= '2014' then gl_balance.creditamount else 0 end) "2014贷方发生额" , sum( case when gl_balance.year= '2015' then gl_balance.creditamount else 0 end) "2015贷方发生额" from gl_balance, bd_accsubj, bd_glorgbookwhere gl_balance.pk_accsubj = bd_accsubj.pk_accsubjand bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbookand bd_glorgbook.glorgbookcode like '01%-0001' and (bd_accsubj.subjcode like '2123%' or bd_accsubj.subjcode like '1131%'or bd_accsubj.subjcode like '1111%')and gl_balance.period<>'00'and gl_balance.year in('2012','2013','2014','2015')group by bd_glorgbook.glorgbookcode, rollup((substr(bd_accsubj.subjcode,1,4), bd_glorgbook.glorgbookname)) order by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4) 通过substr取一级
2015-10-13 08:50:00 计算工程款
select bd_glorgbook.glorgbookcode,nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习--substr(bd_accsubj.subjcode,1,4)科目编码,---1111应收票据（借方），1131应收账款（+贷-借），2123工程结算--以下是贷方sum( case when gl_balance.year= '2012' then gl_balance.creditamount else 0 end) "2012贷方发生额",sum( case when gl_balance.year= '2013' then gl_balance.creditamount else 0 end) "2013贷方发生额",sum( case when gl_balance.year= '2014' then gl_balance.creditamount else 0 end) "2014贷方发生额" , sum( case when gl_balance.year= '2015' and gl_balance.period<=6 then gl_balance.creditamount else 0 end) "2015贷方发生额" from gl_balance, bd_accsubj, bd_glorgbookwhere gl_balance.pk_accsubj = bd_accsubj.pk_accsubjand bd_glorgbook.pk_glorgbook = gl_balance.pk_glorgbookand bd_glorgbook.glorgbookcode like '01%-0001' and (bd_accsubj.subjcode like '2123%' )and gl_balance.period<>'00'and gl_balance.year in('2012','2013','2014','2015')group by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4), bd_glorgbook.glorgbooknameorder by bd_glorgbook.glorgbookcode, substr(bd_accsubj.subjcode,1,4) 2015年6月的工程款中gl_balance需要是<=不能是=，6也不需要引号（=6为当月的余额），必须记账才能取到
归集sql
select bd_accid.accidcode, bd_accid.accidname, sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2012' then fts_voucher_b.creditamount else 0 end) "2012归集额",sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2013' then fts_voucher_b.creditamount else 0 end) "2013归集额",sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2014' then fts_voucher_b.creditamount else 0 end) "2014归集额" , sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '2015' then fts_voucher_b.creditamount else 0 end) "2015归集额" from fts_voucher_b, bd_accid where fts_voucher_b.dr = '0' and fts_voucher_b.pk_corp = '1162' and fts_voucher_b.pk_account = bd_accid.pk_accid -- and bd_accid.accidcode = '011501' --公司 and fts_voucher_b.creditamount <> 0 and substr(fts_voucher_b.interestdate, 1, 4) in('2012','2013','2014','2015') --年份 and fts_voucher_b.pk_ass not in (select freevalueid from gl_freevalue where valuecode = '777777') group by bd_accid.accidcode, bd_accid.accidname order by bd_accid.accidcode 委托中心付款合计sql
select c.accidcode,c.accidname,sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" , sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额" from fts_voucher a,fts_voucher_b b, bd_accid c where a.pk_voucher=b.pk_voucher and b.pk_account=c.pk_accid and a.cyear in('2012','2013','2014','2015') and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)group by c.accidcode,c.accidname order by c.accidcode 待研究
select c.accidcode,c.accidname,
sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" , sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额"from fts_voucher a,fts_voucher_b b, bd_accid c
where a.pk_voucher=b.pk_voucher and b.pk_account=c.pk_accid and a.cyear in('2012','2013','2014','2015') and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制) and fts_voucher_b.pk_ass not in (select freevalueid from gl_freevalue where valuecode = '777777') group by c.accidcode,c.accidname order by c.accidcode2015-10-12 09:31:13 整合 去掉内部转账的委托付款
select c.accidcode,c.accidname,sum( case when a.cyear= '2012' then a.totalcredit else 0 end) "2012借方发生额",sum( case when a.cyear= '2013' then a.totalcredit else 0 end) "2013借方发生额",sum( case when a.cyear= '2014' then a.totalcredit else 0 end) "2014借方发生额" , sum( case when a.cyear= '2015' then a.totalcredit else 0 end) "2015借方发生额" from fts_voucher a,fts_voucher_b b, bd_accid c where a.pk_voucher=b.pk_voucher and b.pk_account=c.pk_accid and a.cyear in('2012','2013','2014','2015') and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)and b.pk_ass not in(select freevalueid from gl_freevalue where valuecode = '777777') group by c.accidcode,c.accidname order by c.accidcode