基础资料
select bd_glorgbook.glorgbookcode,
nvl(replace(bd_glorgbook.glorgbookname,'集团基准账薄',''),'小计')公司名称, --从萝卜那里学习 substr(bd_accsubj.subjcode,1,4)科目编码,---1111应收票据（借方），1131应收账款（+贷-借），2123工程结算
--以下是贷方
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2012借方发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2013借方发生额",
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2014借方发生额" ,
sum( case when gl_balance.year= '' then gl_balance.debitamount else 0 end) "2015借方发生额" ,
--以下是贷方
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2012贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2013贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2014贷方发生额" ,
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','','')
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= '' then gl_balance.creditamount else 0 end) "2012贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2013贷方发生额",
sum( case when gl_balance.year= '' then gl_balance.creditamount else 0 end) "2014贷方发生额" ,
sum( case when gl_balance.year= '' 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<>''
and gl_balance.year in('','','','')
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)= '' then fts_voucher_b.creditamount else 0 end) "2012归集额",
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '' then fts_voucher_b.creditamount else 0 end) "2013归集额",
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '' then fts_voucher_b.creditamount else 0 end) "2014归集额" ,
sum( case when substr(fts_voucher_b.interestdate, 1, 4)= '' then fts_voucher_b.creditamount else 0 end) "2015归集额"
from fts_voucher_b, bd_accid
where fts_voucher_b.dr = ''
and fts_voucher_b.pk_corp = ''
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('','','','') --年份
and fts_voucher_b.pk_ass not in
(select freevalueid from gl_freevalue where valuecode = '')
group by bd_accid.accidcode, bd_accid.accidname
order by bd_accid.accidcode
委托中心付款合计sql
select c.accidcode,c.accidname, sum( case when a.cyear= '' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '' then a.totalcredit else 0 end) "2013借方发生额",
sum( case when a.cyear= '' then a.totalcredit else 0 end) "2014借方发生额" ,
sum( case when a.cyear= '' 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('','','','')
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= '' then a.totalcredit else 0 end) "2012借方发生额",
sum( case when a.cyear= '' then a.totalcredit else 0 end) "2013借方发生额",
sum( case when a.cyear= '' then a.totalcredit else 0 end) "2014借方发生额" ,
sum( case when a.cyear= '' 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('','','','')
and a.datasource=2 --(6上收，2委托，5下拨，3到账通知，0手工填制)
and b.pk_ass not in
(select freevalueid from gl_freevalue where valuecode = '')
group by c.accidcode,c.accidname
order by c.accidcode