9. 使用WPS工作薄连接调试SQL之四（汇总行的合并）

原创2022-05-29 19:06·小草随风1668

我在 第7节 完成了合计行的汇总，在 第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查询前的相关功能设计。

举报
评论 0
    热门：