Excel VBA ADO SQL入门教程012:多表数据合并汇总

今天给大家分享一下如何使用SQL合并多工作表数据。

在SQL中，UNION运算符可以合并两个或多个的SELECT语句的查询结果，因此，我们通常使用它来合并多表数据。

例如，在一个工作簿中存在两个表，一个表名为 '一班'，另一个表名为'二班'，现在我们需要将两个表的数据合并成一张表。

          

SQL语句如下：

• 
  

• 
  

• 
  

SELECT * FROM [一班$] UNION SELECT * FROM [二班$]

结果如下：

          

需要说明的是，UNION会对合并的结果去重复，只保留唯一值；前面讲去重复的时候我们提过，SQL去重复的过程，是先对记录排序，然后再去重复，因此UNION的运算结果是升序排列的不重复记录。

依然以上图所示的数据为例，汇总一班和二班两个班级的学生名单：

• 
  

• 
  

• 
  

SELECT 姓名  FROM [一班$] UNION SELECT 姓名  FROM [二班$]

结果如下：

          

由于使用了UNION运算符，一、二班都存在的'看见星光'，只保留了一个。

如果不需要去重复的操作，可以使用关键字ALL，也就是UNION ALL。

同样汇总一班和二班两个班级的学生名单：

• 
  

• 
  

• 
  

SELECT 姓名 FROM [一班$] UNION ALL SELECT 姓名 FROM [二班$]

结果如下：

        

          

使用了关键字ALL后，一、二班存在的'看见星光'，都被保留了下来；而且姓名的排放顺序是和出现的顺序一致的，并没有进行排序处理。

……

需要说明的是，不管是UNION还是UNION ALL，都要求SELECT语句拥有相同的列数，而且字段的排放顺序必须相同。

当列数不相同时，例如以下语句：

代码看不全可以左右拖动...▼

• 
  

• 
  

• 
  

SELECT 姓名,语文  FROM [一班$] UNION ALL SELECT 姓名  FROM [二班$]

会得到错误提示：

          

而当列的个数相同，但顺序不相同时，例如以下语句：

代码看不全可以左右拖动...▼

• 
  

• 
  

• 
  

SELECT 姓名,语文 FROM [一班$] UNION ALL SELECT 语文,姓名 FROM [二班$]

会得出错误的结果。

          

之所以语句能够运算，但结果不尽人意（姓名列出现了语文的成绩），是因为UNION运算符总是按第一个SELECT语句中字段的排放顺序处理数据的。

第一个SELECT指定了'姓名'字段第1列、'语文'字段第2列，则默认以后的SELECT语句第1列均为'姓名'字段，第2列均为'语文'。

此外，UNION运算符总是将第一个SELECT语句提供的字段名称作为最终查询结果的字段名称。

例如，以下语句只是在第一个SELECT子句中使用了别名，但查询结果中的字段名依然是按照指定别名呈现的。

代码看不全可以左右拖动...▼

• 
  

• 
  

• 
  

SELECT 姓名,语文 AS 语文成绩  FROM [一班$] UNION ALL SELECT 姓名,语文  FROM [二班$]

          

……

在实际汇总多表数据的过程中，难免会碰到表格列数不一致的情况。

例如，以下两个表，名字为'一班'的表字段由姓名、语文、数学和英语构成，而另外一个名为’二班'的表字段只有姓名、语文和英语，并没有数学字段。如果此时我们进行两表数据汇总，应该怎么处理呢？

          

对于缺少的字段可以使用某个值代替。

通常是使用NULL代替，语句如下：

代码看不全可以左右拖动...▼

• 
  

• 
  

• 
  

SELECT 姓名,语文,数学,英语  FROM [一班$] UNION ALL SELECT 姓名,语文,NULL,英语 FROM [二班$]

结果如下：

          

当然，也可以使用其它值代替，例如'未考’，语句如下：

• 
  

• 
  

• 
  

SELECT 姓名,语文,数学,英语  FROM [一班$] UNION ALL SELECT 姓名,语文,'未考',英语  FROM [二班$]

结果如下：

          

……

当需要汇总的表格列数统一，但个数过多时，手工输入SQL语句未免不够灵活方便，此时最好是使用VBA的方式。

以本文的第1张图数据为例，使用VBA+ADO+SQL后的代码如下所示▼

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub SQL_UNION() Dim cnn As Object, rst As Object Dim strPath As String, str_cnn As String Dim strSQL As String, strTemp As String Dim sht As Worksheet, strShtName As String Dim i As Long Set cnn = CreateObject('adodb.connection') strPath = ThisWorkbook.FullName If Application.Version < 12 Then str_cnn = 'Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' & strPath Else str_cnn = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=' & strPath End If cnn.Open str_cnn strTemp = 'SELECT 姓名,语文,数学,英语,' For Each sht In Worksheets strShtName = sht.Name If strShtName <> ActiveSheet.Name Then strSQL = strSQL & strTemp & ''' & strShtName & '' AS 班级 FROM [' & strShtName & '$] UNION ALL ' End If Next strSQL = Left(strSQL, Len(strSQL) - 11) Set rst = cnn.Execute(strSQL) Cells.ClearContents For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1) = rst.Fields(i).Name Next Range('a2').CopyFromRecordset rst cnn.Close Set cnn = NothingEnd Sub

运算结果如下：

          

代码中，变量strTemp指定了需要汇总的字段名称和顺序；之后遍历工作表对象，合并多个SELECT语句，并将工作表名称作为一个字段补充到SELECT子句中，最后使用ADO执行SQL语言获得查询结果。

而当需要汇总的表格标题名称统一，但列数并不统一，表格个数又过多时，可以借助字典先对列标题和排列顺序做一个过滤储存，然后再编写和执行SQL语句……这个问题我们就放到介绍记录集对象时再谈。

小贴士：

您可能见过这样的多表合并SQL语句，使用通配符*代替字段名的描述：

• 
  

• 
  

• 
  

SELECT * FROM  [一班$] UNION ALL SELECT * FROM [二班$]