wps如何运行sql联合查询(7.使用WPS工作薄连接调试SQL之二)

在上一节里,按区分项目的规则,对数据进行了初步汇总,完成了【5. WPS表格报表的SQL数据查询方案设计】中提到的第一步的目标:第一步,先把同一个项目的计划进行初步汇总,形成每条数据都是不同项目的临时查询表。

在这一节中,将逐步完成第二步的目标:第二步,根据第一步生成的临时查询表,对功能分类和单位进行分级汇总,生成五级的汇总临时表,最后把这些分级汇总表进行合并排序,生成最后的报表。这一步要生成排序字段(图 5.3),五级汇总数据行的排序就依靠这个排序字段实现。

wps如何运行sql联合查询(7.使用WPS工作薄连接调试SQL之二)(1)

报表效果 图5.3

如图5.3 报表效果图中,科目名称中第一行 “合计” 的实现。

首先分析一下合计行的列:

  1. 排序为 0;
  2. 类、款、项三个列都是空;
  3. 科目名称为合计;
  4. 浅绿色部分的指标及计划都是根据where条件汇总而来;
  5. 蓝色部分是把项目分类进行“行转列”的操作,行转列使用iif 函数,具体请搜索 “iif函数行转列”。
  6. 无group by 分组条件。

根据以上分析,第二步外层SQL 查询语句如下:

select 0 as [排序], as [类], as [款], as [项], 合计 as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], sum(T.[计划合计]) 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 (/*括号内第一步内层的子查询,取别名为T*/) as T // 第9~11行 IIF函数判断如果不属于以上列举的情况,统一处理为其他项目分类

从上面的语句可以看出,从子查询来源的字段只有4个,分别为T.[指标金额]、T.[已用指标]、T.[计划合计]、T.[项目类别],其他字段都是临时生成,或者基于以上4个字段计算得到。

第二步外层查询的实现基础是第一步内层子查询生成的临时表。临时表的查询具体请参看 【6. 使用WPS工作薄连接调试SQL之一】,第一步使用的SQL语句如下:

select [单位], [项目], avg([指标总金额]) as [指标金额], avg([指标已用金额]) as [已用指标], [指标金额]- [已用指标] as [指标余额], sum( [计划金额]) as [计划合计], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] order by [项目]

根据第二步外层查询需要字段的信息,把上面的内层子查询SQL语句进行精简,只保留需要的4个字段,去掉无用的排序order by 语句:

select avg([指标总金额]) as [指标金额], avg([指标已用金额]) as [已用指标], sum( [计划金额]) as [计划合计], [项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购]

将上面精简过的内部子查询填入第二步外层SQL查询语句,为了便于阅读,将外层查询语句关键字大写:

SELECT 0 as [排序], as [类], as [款], as [项], 合计 as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], sum(T.[计划合计]) 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 avg([指标总金额]) as [指标金额], avg([指标已用金额]) as [已用指标], sum( [计划金额]) as [计划合计], [项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T

将以上SQL语句复制到WPS查询的命令文本中,确定。

查询结果,图7.0(或7.2第一行)

wps如何运行sql联合查询(7.使用WPS工作薄连接调试SQL之二)(2)

图 7.0

仔细分析数据发现这个汇总结果并不正确,错误发生在【指标已用金额、指标可用金额】这两个字段,通过分析源数据行的数据(图7.1),发现部分同一项目出现在途数据未更新到所有行,导致一个项目的【指标已用金额】不一致的现象。

wps如何运行sql联合查询(7.使用WPS工作薄连接调试SQL之二)(3)

源数据分析 图7.1

根据图7.1的分析结果,含在途计划金额=指标已用金额如果需要含在途计划的查询,字段可以精简到3个,将计划金额去掉,在外层查询里做计算即可。

修改为在途计划查询的SQL语句:(图7.2 第三行)

//含在途计划的查询 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

不含在途计划的查询,计划仍然需要合计:(图7.2 第二行)

//不含在途计划的查询 SELECT 0 as [排序], as [类], as [款], as [项], 合计 as [科目名称] , sum(T.[指标金额]) as [指标总金额], sum(T.[已用指标]) as [指标已用金额], [指标总金额]-[指标已用金额] as [指标可用金额], sum(T.[计划合计]) 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 [已用指标], sum([计划金额]) as [计划合计], [项目类别] from [src$] where (left([单位],6)="101013") and ([计划月份] between "01" and "05") group by [单位], [项目], [项目类别], [支出功能分类], [政府经济分类], [部门经济分类], [是否政府采购] ) as T

wps如何运行sql联合查询(7.使用WPS工作薄连接调试SQL之二)(4)

数据查询对比 图7.2

经过对比,显然有在途计划的查询速度更快,因此,在以后的查询中,我将选用有在途计划的查询方案作为示例。

在本节中,实现了报表合计行的查询,在下一节将继续实现类款项的查询。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。