利用聚合函数和SQL语句完成多工作表的汇总查询

蓝字关注，NZ9668获资料信息

 《VBA代码解决方案》系列书作者

头条百家平台 VBA资深创作者

_______________________________

大家好，我们继续VBA数据库解决方案的学习，今天讲解第51讲：利用聚合函数和SQL语句完成多工作表的汇总查询计算。今日的内容看似简单，其实有些难度，希望大家不要放弃，在自己测试的时候要多测试几次。虽然本讲的内容可利用其他的方法也可以实现，但这种方法也不失为一种解决问题的有效手段。

在我的系列书籍中一直在强调“搭积木”的编程思路，主要的内涵：首先是代码不要自己全部的录入，你要做的是把积木放在合适的位置让后去修正代码，其次是建立自己的“积木库”,把自己认为有用的代码放在一起，可以随时利用。你的积木库资料越多，你做程序的思路就会越多。数据库的代码录入更是如此，代码往往很长，千万不要自己去录入。空格，引号，逗号的写法要求是非常严格的。一定要拷贝，然后修正代码，把时间利用到高效的思考上。

今日的内容是讲聚合函数和SQL的结合，从而完成我们的实际工作。如下的实例：我的工作表中有两页格式接近的数据，如下： 

  现在我要把两个工作表的数据提取型号，数量，单价，并把数量按型号汇总，汇总后的数据按型号排序处理。

上面的例子，如果在EXCEL工作表文件中处理要多个步骤来完成，用数据库的一般方案也是比较麻烦的，下面看我们聚合函数的功效吧。

我给出的代码如下;

Sub mynzRecords_51() '第51讲  利用聚合函数和SQL语句完成多工作表的汇总查询计算

 DimcnADO, rsADO As Object

   Dim strPath, strSQL1, strSQL2, strSQL3, strSQL4 As String

   Worksheets("51").Select

   Cells.ClearContents

   Set cnADO = CreateObject("ADODB.Connection")

   Set rsADO = CreateObject("ADODB.Recordset")

    '建立一个ADO的连接

    strPath = ThisWorkbook.FullName

    cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extendedproperties='excel 12.0;hdr=yes;imex=1';data source=" & strPath

    strSQL1 = "select 型号,数量,单价 from [数据$]"

    strSQL2 = "select 型号,数量,单价 from [数据2$]"

    strSQL3 = strSQL1 & " UNION ALL " & strSQL2

    strSQL4 = "select 型号,SUM(数量),单价 from (" & strSQL3 &") GROUP BY 型号,单价"

     arr = Array("型号", "数量", "单价")

    [a1:c1] = arr

    [a65536].End(xlUp).Offset(1, 0).CopyFromRecordset cnADO.Execute(strSQL4)

   cnADO.Close

   Set cnADO = Nothing

   Set rsADO = Nothing

  End Sub

代码截图：

代码讲解：

1   strSQL1 = "select 型号,数量,单价 from [数据$]" 第一个SQL语句完成“数据”工作表的数据提取。

 2  strSQL2 = "select 型号,数量,单价 from [数据2$]" 第二个SQL语句完成“数据2”工作表的数据提取。

 3  strSQL3 = strSQL1 & " UNION ALL" & strSQL2 第三个SQL语句完成strSQL1和strSQL2的组合，这里利用到了聚合函数UNION，此函数的用法大家要注意，按照我给出的代码示例进行即可。

 4  strSQL4 = "select 型号,SUM(数量),单价 from (" & strSQL3 & ") GROUP BY 型号,单价"

第四个SQL语句，通过上面的聚合函数建立一个新的SQL查询。同样这个语句的写法要注意按照我的示例格式进行，不要另辟蹊径，通不过的。

下面看我们代码的运行结果：

今日内容回向：

1  聚合函数在sql中如何应用？

2  如何利用聚合函数完成数据的统计？

_____________________________

觉得有启发，点个“在看”，转给朋友们

欢迎你“留言”，和作者直接交流

更多关联阅读