系列终篇
作为【VBA+SQL查询Excel工作表数据】系列的终篇，本文将继续使用VBA+SQL查询的模板，并详细介绍如何在VBA中使用SQL多表查询语句。由于多表查询的SQL语句相对复杂，建议在熟练掌握前4篇的案例之后，再学习多表查询的SQL语句。
VBA+SQL的查询模板
Sub VBA的SQL查询模板()
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\员工数据库.xlsx'"
Dim sql As String
sql = "select * from [员工花名册$A1:I65536]"
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
Set rs = conn.Execute(sql)
ActiveWorkbook.Sheets.Add before:=ActiveWorkbook.Sheets(1)
ActiveWorkbook.Sheets(1).Name = "SQL查询结果" & Format(Now, "yyyymmddhhmmss")
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
ActiveWorkbook.Sheets(1).Cells(1, i + 1) = rs.Fields(i).Name
Next
ActiveWorkbook.Sheets(1).Range("a2").CopyFromRecordset rs
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
End Sub案例数据
SQL多表查询语句
以下SQL多表查询语句，只要将代码第7行的sql语句替换即可。
1.隐式内连接（通过where关键字）
sql = "select 工资表.工号,工资表.姓名,部门,实发金额 from [工资表$A3:M65536] as gzb,[员工花名册$A1:I65536] as yghmc where gzb.工号=yghmc.工号" |
2.显式内连接（inner join）
sql = "select 部门,avg(基础工资) as 平均工资 from [工资表$A3:M65536] gzb inner join [员工花名册$A1:M65536] yghmc on gzb.工号=yghmc.工号 group by 部门 having avg(基础工资)<2000" |
3.左连接（left join）
sql = "select * from [工资表$A3:M65536] gzb left join [员工花名册$A1:I65536] yghmc on gzb.工号=yghmc.工号" |
4.右连接（right join）
sql = "select * from [工资表$A3:M65536] gzb right join [员工花名册$A1:I65536] yghmc on gzb.工号=yghmc.工号" |
左连接vs右连接：左连接，将左表作为基础表，并列出左表所有的查询信息，而右表只列出ON条件下与左表匹配的查询信息；右连接的原理，亦相同。
5.全连接（full outer join）
注：★SQL语句操作Excel工作表数据，不支持全连接！★
6.自连接
例：查询英文名重复的员工信息
sql = "select t1.姓名,t1.英文名 from [员工花名册$A1:I65536] t1 inner join [员工花名册$A1:I65536] t2 on t1.英文名=t2.英文名 where t1.工号<>t2.工号 " |
7.嵌套连接
例：查询年龄>平均年龄的员工信息
sql = "select 姓名,部门,出生日期 from [员工花名册$A1:I65536] where year(now)-year(出生日期)>(select avg(year(now)-year(出生日期)) as 年龄 from [员工花名册$A1:I65536])" |
8.union all和union
例：查询年龄最大的五位员工信息以及年龄最小的五位员工信息
sql = "select top 5 * from [员工花名册$A1:I65536] order by 出生日期 union all select top 5 * from [员工花名册$A1:I65536] order by 出生日期 desc " |
注：union all连接全部记录，union去除重复记录。