with ds1 as (

select t1.xmdm,t1.xmmc,loccurramt

from SJYS t1

left join SJYS_DT1 t2 on t1.id=t2.mainid

left join SJYS_KMDM t3 on t2.erpxmdm=t3.glacct2

where t3.fiscalyear=2020 and t3.companycode='W68' and t3.postperiod=1

),

ds2 as (

select t1.xmdm,t1.xmmc,

ds1.loccurramt

from SJYS t1

left join ds1 on t1.xmdm=ds1.xmdm

order by t1.xh

),

ds3 as (

select 'FY00' xmdm,sum(loccurramt) loccurramt from ds2

union all

select substr(xmdm,1,2)||'00' xmdm,sum(loccurramt) loccurramt from ds2

where xmdm <> 'FY00'

group by substr(xmdm,1,2)

union all

select substr(xmdm,1,4) xmdm,sum(loccurramt) loccurramt from ds2

where substr(xmdm,3,2) <> '00'

group by substr(xmdm,1,4)

union all

select substr(xmdm,1,6) xmdm,sum(loccurramt) loccuramt from ds2 where length(xmdm)=6

group by substr(xmdm,1,6)

)

select ds3.xmdm,t1.xmmc,ds3.loccurramt from ds3

join SJYS t1 on t1.xmdm=ds3.xmdm

order by t1.xh

已实现