(期初金额+[2019发生额]+[2020发生额]+[2021发生额]+[2022发生额] )as 累计发生
from(select(case when qc.citem_class is Not null then qc.citem_class
when fs_2020.citem_class is Not null then fs_2020.citem_class
when fs_2021.citem_class IS not NULL then fs_2021.citem_class
when fs_2022.citem_class IS not null then fs_2022.citem_class else qc.citem_class end ) 项目大类 ,
(case when qc.citem_id is Not null then qc.citem_id
when fs_2020.citem_id is Not null then fs_2020.citem_id
when fs_2021.citem_id IS not NULL then fs_2021.citem_id
when fs_2022.citem_id IS not null then fs_2022.citem_id else qc.citem_id end ) 项目目录,
(case when qc.ccode is Not null then qc.ccode
when fs_2020.ccode is Not null then fs_2020.ccode
when fs_2021.ccode IS not NULL then fs_2021.ccode
when fs_2022.ccode IS not null then fs_2022.ccode else qc.ccode end ) 科目编码,
(case when qc.ccode_name is Not null then qc.ccode_name
when fs_2020.ccode_name is Not null then fs_2020.ccode_name
when fs_2021.ccode_name IS not NULL then fs_2021.ccode_name
when fs_2022.ccode_name IS not null then fs_2022.ccode_name else qc.ccode_name end ) 科目名称,
isnull(qc.mb ,0) 期初金额,
isnull(fs_2019.md ,0) as '2019发生额'
,isnull(fs_2020.md,0) '2020发生额', isnull(fs_2021.md,0) '2021发生额'
, isnull(fs_2022.md,0) '2022发生额'
from
--qichu
(select citem_class,citem_id,gl_accass.ccode,ccode_name,sum(mb )mb
from GL_accass left join code on gl_accass.ccode=code.ccode
where gl_accass.iyear=2019 and iperiod=8 and gl_accass.ccode in
('5401010201','5401019201','5401010202','5401010203','5401010204','5401010205','5401010206','5401010207','5401010208','5401010209','5401010210','5401010211',
'5401010212','5401019213','5401010214','5401010215','5401010216','54010103') and code.iyear=2019
group by citem_class,citem_id,gl_accass.ccode,ccode_name)qc
full join
--2019
(select citem_class,citem_id,gl_accass.ccode,ccode_name,sum(md )md
from GL_accass left join code on gl_accass.ccode=code.ccode where gl_accass.iyear=2019 and gl_accass.ccode in
('5401010201','5401019201','5401010202','5401010203','5401010204','5401010205','5401010206','5401010207','5401010208','5401010209','5401010210','5401010211',
'5401010212','5401019213','5401010214','5401010215','5401010216','54010103')and code.iyear=2019
group by citem_class,citem_id,gl_accass.ccode,ccode_name)fs_2019 on qc.citem_class=fs_2019.citem_class and qc.citem_id=fs_2019.citem_id and qc.ccode =fs_2019.ccode
full join
--2020
(select citem_class,citem_id,gl_accass.ccode,ccode_name,sum(md)md
from GL_accass left join code on gl_accass.ccode=code.ccode where gl_accass.iyear=2020 and gl_accass.ccode in
('5401010201','5401019201','5401010202','5401010203','5401010204','5401010205','5401010206','5401010207','5401010208','5401010209','5401010210','5401010211',
'5401010212','5401019213','5401010214','5401010215','5401010216','54010103') and code.iyear=2020
group by citem_class,citem_id,gl_accass.ccode,ccode_name)fs_2020 on qc.citem_class=fs_2020.citem_class and qc.citem_id=fs_2020.citem_id and qc.ccode =fs_2020.ccode
full join
--2021
(select citem_class,citem_id,gl_accass.ccode,ccode_name,sum(md )md
from GL_accass left join code on gl_accass.ccode=code.ccode where gl_accass.iyear=2021 and gl_accass.ccode in
('5401010201','5401019201','5401010202','5401010203','5401010204','5401010205','5401010206','5401010207','5401010208','5401010209','5401010210','5401010211',
'5401010212','5401019213','5401010214','5401010215','5401010216','54010103')
and code.iyear=2021
group by citem_class,citem_id,gl_accass.ccode,ccode_name)fs_2021 on qc.citem_class=fs_2021.citem_class and qc.citem_id=fs_2021.citem_id and qc.ccode =fs_2021.ccode
full join
--2022
(select citem_class,citem_id,gl_accass.ccode,ccode_name,sum(md )md
from GL_accass left join code on gl_accass.ccode=code.ccode where gl_accass.iyear=2022 and gl_accass.ccode in
('5401010201','5401019201','5401010202','5401010203','5401010204','5401010205','5401010206','5401010207','5401010208','5401010209','5401010210','5401010211',
'5401010212','5401019213','5401010214','5401010215','5401010216','54010103') and code.iyear=2022
group by citem_class,citem_id,gl_accass.ccode,ccode_name)fs_2022 on qc.citem_class=fs_2022.citem_class and qc.citem_id=fs_2022.citem_id and qc.ccode =fs_2022.ccode
) a
left join fitemss00 on fitemss00.citemcode=a.项目目录
总结建议大家在使用的时候，只需要把科目代码换成自己公司实际财务科目设置的代码即可。
关注我，一起学习数据库知识。
