Excel VBA ADO SQL入门教程012：UNION多表数据合并 / 四六文摘

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图为例：

Sub Sql_UNION()

Dim cnn As Object, rst As Object

Dim Mypath As String, Str_cnn As String

Dim Sql As String, Sql1 As String

Dim Sht As Worksheet, Sht_name As String

Dim i As Long

Set cnn = CreateObject('adodb.connection')

Mypath = ThisWorkbook.FullName

If Application.Version < 12 Then

Str_cnn = 'Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' & Mypath

Else

Str_cnn = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=' & Mypath

End If

cnn.Open Str_cnn

Sql1 = 'SELECT 姓名,语文,数学,英语,'

For Each Sht In Worksheets

Sht_name = Sht.Name

If Sht_name <> ActiveSheet.Name Then

Sql = Sql & Sql1 & ''' & Sht_name & '' AS 班级 FROM [' & Sht_name & '$] UNION ALL '

End If

Next

Sql = Left(Sql, Len(Sql) - 11)

Set rst = cnn.Execute(Sql)

Cells.ClearContents

For i = 0 To rst.Fields.Count - 1

Cells(1, i 1) = rst.Fields(i).Name

Range('a2').CopyFromRecordset rst

cnn.Close

Set cnn = Nothing

End Sub

运算结果如下：

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

图文作者：看见星光

Excel VBA ADO SQL入门教程012：UNION多表数据合并

相关推荐