我在 完成了合计行的汇总，在 第8节 完成了类、款、项和单位的汇总。

在本节里我将把这些单独的汇总语句合并，并生成最终的报表。

合并SQL 结果集使用 UNION ALL 语句。

使用UNION ALL语句时要注意，要合并的SQL结果集必须具有相同的字段。

因此，第7节与第8节的所有语句的别名都是一致的。

每个汇总行语句都有一个[排序]列，使用order by 语句按[排序]列内容的顺序排序就可以得到所要的报表。

合并后的SQL语句如下：

SELECT 0 as [排序], '' as [类], '' as [款], '' as [项], '合计' as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], [指标已用金额] as [计划金额(含在途)], sum( iif( T.[项目类别]='工资福利支出', T.[已用指标],0)) as [工资福利支出], sum( iif( T.[项目类别]='对个人和家庭补助支出', T.[已用指标],0)) as [对个人和家庭补助支出], sum( iif( T.[项目类别]='公用经费', T.[已用指标],0)) as [公用经费], sum( iif( T.[项目类别]='部门预算项目', T.[已用指标],0)) as [部门预算项目], sum( iif( T.[项目类别]='专项资金项目', T.[已用指标],0)) as [专项资金项目], sum( iif( T.[项目类别]<>'工资福利支出' and T.[项目类别]<>'对个人和家庭补助支出' and T.[项目类别]<>'公用经费' and T.[项目类别]<>'部门预算项目' and T.[项目类别]<>'专项资金项目', T.[已用指标],0)) as [其他项目] FROM ( select max([指标总金额]) as [指标金额], max([指标已用金额]) as [已用指标],[项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T UNION ALL SELECT T.[类] & '0000' as [排序], T.[类], '' as [款], '' as [项], ' ' & L.km1mc as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], [指标已用金额] as [计划金额(含在途)], sum( iif( T.[项目类别]='工资福利支出', T.[已用指标],0)) as [工资福利支出], sum( iif( T.[项目类别]='对个人和家庭补助支出', T.[已用指标],0)) as [对个人和家庭补助支出], sum( iif( T.[项目类别]='公用经费', T.[已用指标],0)) as [公用经费], sum( iif( T.[项目类别]='部门预算项目', T.[已用指标],0)) as [部门预算项目], sum( iif( T.[项目类别]='专项资金项目', T.[已用指标],0)) as [专项资金项目], sum( iif( T.[项目类别]<>'工资福利支出' and T.[项目类别]<>'对个人和家庭补助支出' and T.[项目类别]<>'公用经费' and T.[项目类别]<>'部门预算项目' and T.[项目类别]<>'专项资金项目', T.[已用指标],0)) as [其他项目] FROM ( select left([支出功能分类],3) as [类], max([指标总金额]) as [指标金额], max([指标已用金额]) as [已用指标],[项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T LEFT JOIN [set$] as L ON T.[类]=L.[km1] GROUP BY T.[类], L.km1mc UNION ALL SELECT T.[款] & '00' as [排序], '' as [类], T.[款], '' as [项], ' ' &L.km2mc as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], [指标已用金额] as [计划金额(含在途)], sum( iif( T.[项目类别]='工资福利支出', T.[已用指标],0)) as [工资福利支出], sum( iif( T.[项目类别]='对个人和家庭补助支出', T.[已用指标],0)) as [对个人和家庭补助支出], sum( iif( T.[项目类别]='公用经费', T.[已用指标],0)) as [公用经费], sum( iif( T.[项目类别]='部门预算项目', T.[已用指标],0)) as [部门预算项目], sum( iif( T.[项目类别]='专项资金项目', T.[已用指标],0)) as [专项资金项目], sum( iif( T.[项目类别]<>'工资福利支出' and T.[项目类别]<>'对个人和家庭补助支出' and T.[项目类别]<>'公用经费' and T.[项目类别]<>'部门预算项目' and T.[项目类别]<>'专项资金项目', T.[已用指标],0)) as [其他项目] FROM ( select left([支出功能分类],5) as [款], max([指标总金额]) as [指标金额], max([指标已用金额]) as [已用指标],[项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T LEFT JOIN [set$] as L ON T.[款]=L.[km2] WHERE L.km2mc <> '' GROUP BY T.[款], L.km2mc UNION ALL SELECT T.[项] as [排序], '' as [类], '' as [款], T.[项], ' ' & L.km3mc as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], [指标已用金额] as [计划金额(含在途)], sum( iif( T.[项目类别]='工资福利支出', T.[已用指标],0)) as [工资福利支出], sum( iif( T.[项目类别]='对个人和家庭补助支出', T.[已用指标],0)) as [对个人和家庭补助支出], sum( iif( T.[项目类别]='公用经费', T.[已用指标],0)) as [公用经费], sum( iif( T.[项目类别]='部门预算项目', T.[已用指标],0)) as [部门预算项目], sum( iif( T.[项目类别]='专项资金项目', T.[已用指标],0)) as [专项资金项目], sum( iif( T.[项目类别]<>'工资福利支出' and T.[项目类别]<>'对个人和家庭补助支出' and T.[项目类别]<>'公用经费' and T.[项目类别]<>'部门预算项目' and T.[项目类别]<>'专项资金项目', T.[已用指标],0)) as [其他项目] FROM ( select left([支出功能分类],7) as [项], max([指标总金额]) as [指标金额], max([指标已用金额]) as [已用指标],[项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T LEFT JOIN [set$] as L ON T.[项]=L.[km3] WHERE L.km3mc <> '' GROUP BY T.[项], L.km3mc UNION ALL SELECT T.[km] & T.[dwbm] as [排序], '' as [类], '' as [款], '' as [项], ' ' & L.[dwmc] as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], [指标已用金额] as [计划金额(含在途)], sum( iif( T.[项目类别]='工资福利支出', T.[已用指标],0)) as [工资福利支出], sum( iif( T.[项目类别]='对个人和家庭补助支出', T.[已用指标],0)) as [对个人和家庭补助支出], sum( iif( T.[项目类别]='公用经费', T.[已用指标],0)) as [公用经费], sum( iif( T.[项目类别]='部门预算项目', T.[已用指标],0)) as [部门预算项目], sum( iif( T.[项目类别]='专项资金项目', T.[已用指标],0)) as [专项资金项目], sum( iif( T.[项目类别]<>'工资福利支出' and T.[项目类别]<>'对个人和家庭补助支出' and T.[项目类别]<>'公用经费' and T.[项目类别]<>'部门预算项目' and T.[项目类别]<>'专项资金项目', T.[已用指标],0)) as [其他项目] FROM ( select left( [支出功能分类], instr([支出功能分类], '-') -1 ) as [km], left([单位],6) as [dwbm], max([指标总金额]) as [指标金额], max([指标已用金额]) as [已用指标],[项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T LEFT JOIN [set$] as L ON T.[dwbm]=L.[dwbm] GROUP BY T.[km], T.[dwbm], L.[dwmc] ORDER BY [排序]

查询结果如图9.0：

合并查询报表 图9.0

至此，实现了报表的SQL查询方案。

为了能动态的查询单位、计划月份和其他信息，只要更改更改子查询中的条件就可以了，将where (left([单位],6)="101013") and ([计划月份] between "01" and "05") 这条语句替换成 “?”，并保存到报表的工作簿的某一单元格里，使用JS宏进行动态替换，并执行查询，就能达到我们的目的。

下一节，将进行报表JSA查询前的相关功能设计。