/*a与b表都是地市的字段没有地区的字段，都是项目烦的字段没有项目类的字段,其它的参考下面你自己完善上去*/

SELECT 

${IF(LEN(AREA)>0,"a.地市,","")}

${IF(LEN(XMFL)>0,"a.项目段,","")}

(a.[实际]-b.[往月实际]) as 别名1,

(a.[暂估]-b.[暂估]) as 别名2,

(a.[本币余额]-b.[本币余额]) as 别名3,

/*以下的内容参考着改，自己写完。*/

([一个月内]-[一个月内]),

([1至2个月]-[1至2个月]),

([2至3个月]-[2至3个月]),

([3至6个月]-[3至6个月]),

([6月至1年]-[6月至1年]),

([1年至2年]-[1年至2年]),

([2年至3年]-[2年至3年]),

([3年至4年]-[3年至4年]),

([4年至5年]-[4年至5年]),

([5年以上]-[5年以上])

FROM

(

SELECT * FROM(

SELECT 

${IF(LEN(AREA)>0,"地市,","")}

${IF(LEN(XMFL)>0,"项目段,","")}

SUM(case when  科目描述 like'%实际%' then 本币余额  else 0 end)/10000  as [实际],

SUM(case when  科目描述 like'%暂估%'   then 本币余额  else 0 end)/10000  as [暂估],

SUM(case when  [科目描述]<> '未核销收款'  then 本币余额  else 0 end)/10000  as [本币余额],

SUM(case when [科目描述]<> '未核销收款'  then [1月以内] else 0 end)/10000 as [一个月内],

SUM(case when [科目描述]<> '未核销收款'  then [1至2个月] else 0 end )/10000 as [1至2个月],

SUM(case when [科目描述]<> '未核销收款'  then [2至3个月] else 0  end )/10000 as [2至3个月],

SUM(case when [科目描述]<> '未核销收款'  then  [3至6个月] else 0 end )/10000 as [3至6个月],

SUM(case when [科目描述]<> '未核销收款'  then [6月至1年] else 0 end) /10000 as [6月至1年],

SUM(case when [科目描述]<> '未核销收款'  then [1年至2年] else 0 end )/10000 as [1年至2年],

SUM(case when [科目描述]<> '未核销收款'  then [2年至3年] else 0 end )/10000 as [2年至3年],

SUM(case when [科目描述]<> '未核销收款'  then [3年至4年] else 0 end)/10000 as [3年至4年],

SUM(case when [科目描述]<> '未核销收款'  then [4年至5年] else 0 end )/10000  as [4年至5年],

SUM(case when [科目描述]<> '未核销收款'  then [5年以上] else 0 end )as [5年以上]

  FROM 应收预收账龄余额表 where left([科目],4)='1131' and 来源='应收账款' 

  and 日期='2023-03-23'  and 公司段说明='分公司'

  ${IF(LEN(AREA)>0," AND 地市 in ('"+AREA+"')","")}

  ${IF(LEN(XMFL)>0," AND 项目段 in ('"+XMFL+"')","")} 

  ${IF(LEN(XMFL)>0&&LEN(AREA)>0," group by 地市,项目段","")}

  ${IF(LEN(XMFL)>0&&LEN(AREA)=0," group by 项目段","")}

  ${IF(LEN(XMFL)=0&&LEN(AREA)>0," group by 地市","")}

) a

left JOIN 

(

SELECT

${IF(LEN(AREA)>0,"地市,","")}

${IF(LEN(XMFL)>0,"项目段,","")}

SUM(case when  科目描述 like'%实际%' then 本币余额  else 0 end)/10000  as [往月实际],

SUM(case when  科目描述 like'%暂估%'   then 本币余额  else 0 end)/10000  as [往月暂估],

SUM(case when  [科目描述]<> '未核销收款'  then 本币余额  else 0 end)/10000  as [往月本币余额],

SUM(case when [科目描述]<> '未核销收款'  then [1月以内] else 0 end)/10000 as [往月一个月内],

SUM(case when [科目描述]<> '未核销收款'  then [1至2个月] else 0 end )/10000 as [往月1至2个月],

SUM(case when [科目描述]<> '未核销收款'  then [2至3个月] else 0  end )/10000 as [往月2至3个月],

SUM(case when [科目描述]<> '未核销收款'  then  [3至6个月] else 0 end )/10000 as [往月3至6个月],

SUM(case when [科目描述]<> '未核销收款'  then [6月至1年] else 0 end) /10000 as [往月6月至1年],

SUM(case when [科目描述]<> '未核销收款'  then [1年至2年] else 0 end )/10000 as [往月1年至2年],

SUM(case when [科目描述]<> '未核销收款'  then [2年至3年] else 0 end )/10000 as [往月2年至3年],

SUM(case when [科目描述]<> '未核销收款'  then [3年至4年] else 0 end)/10000 as [往月3年至4年],

SUM(case when [科目描述]<> '未核销收款'  then [4年至5年]else 0 end )/10000  as [往月4年至5年],

SUM(case when [科目描述]<> '未核销收款'  then [5年以上] else 0 end )as [往月5年以上]

  FROM 应收预收账龄余额表 where left([科目],4)='1131' and 来源='应收账款' 

  and 日期='2023-02-27'  and 公司段说明='分公司'

  ${IF(LEN(AREA)>0," AND 地市 in ('"+AREA+"')","")}

  ${IF(LEN(XMFL)>0," AND 项目段 in ('"+XMFL+"')","")} 

  ${IF(LEN(XMFL)>0&&LEN(AREA)>0," group by 地市,项目段","")}

  ${IF(LEN(XMFL)>0&&LEN(AREA)=0," group by 项目段","")}

  ${IF(LEN(XMFL)=0&&LEN(AREA)>0," group by 地市","")}

) b

  on  ${IF(LEN(XMFL)>0&&LEN(AREA)>0," a.地市=b.地市,a.项目段=b.项目段","")}

  ${IF(LEN(XMFL)>0&&LEN(AREA)=0," group by a.项目段=b.项目段","")}

  ${IF(LEN(XMFL)=0&&LEN(AREA)>0," group by a.地市=b.地市","")}

)main