基础资料
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_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 '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_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 '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.glorgbookname order 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.accidcode
2015-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