8.使用WPS工作薄连接调试SQL之三（类款项及单位汇总的实现）

在上一节，实现了报表里合计行的汇总，在本节，我将实现合计行下面的类、款、项和单位的汇总。

报表结构如图8.0：

报表结构 图8.0

下面分析一下类、款、项和单位汇总的特点：

1、排序列：

类、款、项汇总行的排序编码都是7位。

其中，类的后四位数字补零；款的后两位补零。

（类款项科目编码具体解析规则请参考 内容）

单位汇总行的排序编码是13位。项科目编码 + 单位编码合并而成。

2、类、款、项 列：

只有类、款、项汇总行显示科目的相应编码，其他都为空。

3、科目名称列：

显示相应汇总行的科目名称或单位名称。

类、款、项的科目名称根据数据源表src的支出功能分类列提取 “-” 前面的编码，外连接提取set中的km1、km2、km3 中的名称（图8.1）；

单位名称根据数据源表src的单位列提取 “-” 前面的编码，外连接提取set中的dwbm中的单位名称（图8.1）（不直接截取单位列“-”后面的名称，是因为源数据可能是单位全称，而报表中使用单位简称）；

注意：set 表里涉及到的编码，必须是文本格式的，如果不是文本格式，要使用【分列】功能改成文本格式。

set表部分 图8.1

4、报表中科目名称后面的列与合计行后面的列相同：[指标总金额]、[指标已用金额]、[指标可用金额]、[计划金额合计]、[工资福利支出]、[对个人和家庭补助支出]、[公用经费]、[部门预算项目]、[专项资金项目]、[其他项目]。

下面，根据 第7节 合计汇总行的语句，构造出类汇总行的语句：

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

结果如图8.2：

类汇总行 图8.2

对以上SQL语句的说明：

1. 字符串连接使用 &，类科目名称前面加入了四个空格，以便更好的缩进。
2. 根据外层查询需要的类科目编码和名称，内层查询使用 left([支出功能分类],3) as [类]，这个函数截取3个字符，并使用别名[类]。
3. T LEFT JOIN [set$] as L ON T.[类]=L.[km1]，加粗的关键字就是左连接语句，这个语句把 T 这个表和 L 表根据ON后面的条件连接起来，用等式左边的 T.[类] 为依据，匹配右边的 L.[km1]。
4. 类汇总行与合计汇总行不同，类汇总行出现了 “真实字段”，T.[类]和L.km1mc，并使用了聚合函数sum，所以后面要使用GROUP BY T.[类], L.km1mc 将没有使用聚合函数的两个字段列举出来。

下面构造出款汇总行语句：

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

结果如图8.3：

款汇总行 图8.3

款汇总行与类汇总行SQL语句不同的地方：

//第一行差异，排序提取字段不同，类三位加四个0，款是5位加两个0 //真实字段不同分别为T.[类]、L.km1mc；T.[款]、L.km2mc。 //科目名称的缩进不同 SELECT T.[类] & '0000' as [排序], T.[类], '' as [款], '' as [项], ' ' & L.km1mc as [科目名称] SELECT T.[款] & '00' as [排序], '' as [类], T.[款], '' as [项], ' ' & L.km2mc as [科目名称]

//子查询提取[支出功能分类]的长度不一致 select left([支出功能分类],3) as [类] select left([支出功能分类],5) as [款]

//款汇总行多了 WHERE L.km2mc <> '' 这个过滤条件，这个有什么用呢？ //假如 [支出功能分类] 这个字段里面有特殊的科目，这个科目只有一级，没有二三级科目 //一般的科目都是这样：2010801-行政运行，前面编码是满7位的 //而特殊科目编码可能不满7位：227-预备费、23201-中央政府国内债务付息支出 //系统存在这种只到类、款科目的功能分类编码，这样就要把取值为空的记录去掉 LEFT JOIN [set$] as L ON T.[类]=L.[km1] GROUP BY T.[类], L.km1mc LEFT JOIN [set$] as L ON T.[款]=L.[km2] WHERE L.km2mc <> '' GROUP BY T.[款], L.km2mc

下面继续构造项汇总行SQL语句：

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

查询结果如图8.4

项汇总行 图8.4

需要注意的是，为何项的科目名称不从src的支出功能分类列 “-” 后面提取？原因还是上面说到的，[支出功能分类] 这个字段里面有特殊的科目，科目编码不一定都是7位的，所以使用

//子查询提取[支出功能分类]的长度不一致 left([支出功能分类],7) as [项] left([支出功能分类],5) as [款]

这两个函数截取的编码不一定对，在类似于

T LEFT JOIN [set$] as L ON T.[项]=L.[km3] T LEFT JOIN [set$] as L ON T.[款]=L.[km2]

这样的左连接匹配时，会找不到L.[km2]或者L.[km3]这个编码，出现L.km2mc或者L.km3mc为空的记录，这些记录在本级汇总时是无用的。

下面构造单位汇总行SQL语句：

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]

查询结果如图8.5

单位汇总行 图8.5

//将 提取的科目编码和单位编码合并为[排序]字段 SELECT T.[km] & T.[dwbm] as [排序]

//第3行代码 别名[km]中函数的意思是从左边截取到“-”这个字符为止 select left([支出功能分类],7) as [项] select left( [支出功能分类], instr([支出功能分类], '-') -1 ) as [km], left([单位],6) as [dwbm]

到此为止，把报表效果图中所有的汇总行都单独写出来了。

下一节，我会把这些单独的汇总行合并到一起。