在VBA中使用SQL

VBA在处理大量的数据/计算时如果使用常规方法会比较慢，因此需要对其进行性能优化以提高运行速度，一般的方法是数组计算或者sql计算。SQL计算的速度最快，限制也是最多的，数组速度其次，灵活性也更高

如果要在vba中调用sql处理数据基本可以遵循一个套路，只要修改其中的SQL语句即可

调用sql处理数据VBA代码如下，其中’##### #####中的地方是每次运行时要根据情况修改的：

Sub Sql_Query()

 Dim Conn As Object, Rst As Object
 Dim strConn As String, strSQL As String
 Dim i As Integer, PathStr As String
 Set Conn = CreateObject("ADODB.Connection")
 Set Rst = CreateObject("ADODB.Recordset")
 PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
 Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接(不同版本的excel连接是不同的)
 Case Is <= 11
    strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
 Case Is >= 12
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
 End Select

 strSQL = "Select * FROM [rawdata$]"   '####在这里改SQL查询语句####


 Conn.Open strConn '打开数据库链接
 Set Rst = Conn.Execute(strSQL)     '执行查询，并将结果输出到记录集对象
 With ThisWorkbook.Sheets("sql data")     '#####在这里更改输出的位置对应的表名####
      .Cells.Clear
      For i = 0 To Rst.Fields.Count - 1    '填写标题
        .Cells(1, i + 1) = Rst.Fields(i).Name    '在第一行输出字段名
      Next i
      .Range("A2").CopyFromRecordset Rst     '从A2单元格开始输出
      .Cells.EntireColumn.AutoFit '自动调整列宽
 End With
 Rst.Close  '关闭数据库连接
 Conn.Close
 Set Conn = Nothing
 Set Rst = Nothing

End Sub

接下来，开始学习SQL语句语法

1.基于一张工作表的查询语法

格式： select [DISTINCT] [TOP<数值> [PERCENT] <列标题> [[as]] <别名列标题>] from <表或查询1>[AS]<别名1>],<表或查询2>[AS]<别名2>],[where<筛选条件>][order by<排序项>[asc ▏ desc]]

说明：

1、<>表示必选项，”[]”表示可选项，”▏”表示多选一。
2、DISTINCT:消除取重复的行
3、TOP 数值：显示前几条记录
4、TOP 数值 percent：显示前面分之多少条记录
5、<列标题>[[as]]<别名列标题>:给标题列重新命一个新名称
6、where<筛选条件>：条件语句
7、排序，如果要按两个或两个以上字段，那么字段与字段之间用豆号隔开，asc升序，为默认值，desc降序。

select关键字

如果要显示显示所有字段的记录的语法结构如下：

SELECT 列字段名1,列字段名2,列字段名3 FROM [工作表名称$]

select * from [sheet1$]
--或者
select ID,name,address,score from [sheet1$]

语句1中的*号是代表全部列，语句 2中，是写上全部列字段的名称,如果工作表没有列标题，用F1,F2,F3,F4…..这样代替

As关键字

使用别名

SELECT 姓名 AS Name FROM [Sheet1$]

注意事项：
1.在SQL语句中SQL语句英文不区分大小写，但标点符号必须是英文半角状态下输入，字段名也必须跟原来的一样。
2.使用SQL语句的时候，必须避免列字段中使用下面的特殊字符：
空格、双引号（”）、撇（’）、数字标记（#）、百分号(%)、大于号（>）、小于号(<)、叹号(!)、句号(.)、方括号（[或]）、星号（*）、美元符号($)、分号（;）、脱字号（^）、圆括号（（或））、加号（+）、反斜杠（\或/）。
如果在源数据表的列字段使用了这些特殊字符，那么在使用SQL语句列出各字段的数据时，就会发生错误。为了规范使用SQL语句，在对数据源字段命名时，尽量避免使用这些特殊字符。

distinct关键字

功能是去重复值只保留一条记录。语法结构为：

SELECT DISTINCT 要去重复值的字段1,要去重复值的字段2 FROM [工作表名$]

Select Distinct name,ID From [sheet1$]

where关键字

按条件筛选使用SQL关键词 WHERE查询中的条件指定要满足什么标准信息，去掉不满足条件的数据（删除用户不要的数据）。
WHERE语句中可以有多个条件，条件之间可以用操作符AND 或者OR进行连接。
WHERE语句的语法结构如下：

SELECT 列字段名称 FROM [表名称$] WHERE 列字段名 运算符 值

运算符包含大于、小于、等于、不等于、大于或等于、小于或等于、IN、 BETWEEN、AND等。

SELECT * FROM [Sheet1$] where 消费金额=100
--提取消费金额等于100的数据

order by关键字

ORDER BY的语句使用对于ORDER BY 语句而言，默认值是升序排列，通常是不指定它。但升序的关键词为ASC,降序为DESC。语法结构如下：

SELECT 列字段名 FROM [工作表名称$] ORDER BY 指定列字段名 升序(降序)

按成绩进行升序排序的SQL语句如下：

Select * FROM [Sheet1$] ORDER BY 成绩 ASC

Top关键字

TOP按顺序提取前n行的记录,语法结构如下：

SELECT TOP 3 * FROM [工作表名$]

如提取成绩前三名的记录

Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY score

其中[Sheet1$A1:C17]这个表示工作表名Sheet1的工作表A1：C17的这个单元格区域，加上了指定的单元格区域为数据。可以根据自己的实际情况，来改变.
不在同一张表上显示结果，而且数据源规范，就可以直接用[工作表名$]。或者书写的时候写上列名不用星号（”*”）
这两个也是和标准sql之间差别较大的地方

TOP 与 PERCENT 组合在SQL语句中的使用可以按照百分比提取数据,例如按成绩降序排列提取前30%

Select TOP 30 PERCENT * FROM [Sheet1$A1:C17]  ORDER BY 成绩 DESC

聚合函数

SQL聚合函数包括sum,count,avg,max,min等，与excel的公式有同样的效果，但是在大数据量的情况下速度会提升很多，而且在多条件求和时会比使用sumifs简单，直观的多
多条件求和的语法结构为：

SELECT 分类字段1, 分类字段2 , SUM(统计字段) as 产品总数 From [Sheet1$] GROUP BY 分类字段1, 分类字段2

例如按照日期和姓名汇总产品数：

SELECT 日期, 姓名, SUM(产品数) as 产品总数 From [Sheet1$] GROUP BY 日期, 姓名

日期数据在SQL表达式中的应用

在SQL表达式运算符条件中，要查询日期和时间类型的数据，需要在数据值两端加上井字符号（#）以表示日期类型。
日期可以有多种表示方式，最符合中国人的习惯是“年-月-日”或“年/月/日”的表示方式。也就是说年月日之间的分隔符可以用“-”或“/”。
例如表示2013年10月1日
可以采用以下表达式：

年月日
#2013-10-1#
年日月
#2013-1-10#
年月日
#2013/10/1#
日月年
#1/10/2013/#
月日年
#10/1/2013#
以表达式在系统无错识别最高的应该是
月/日/年
#10/1/2013#

如何查询二个日期间的数据

Where …. AND…语句，例如查找大于等于2013年10月1日，小于等于2013年10月7日的数据，的SQL表达式为：

SELECT * From [Sheet1$A:C] where 日期>=#10/1/2013# And 日期<=#10/7/2013#

还可以以单元为动态引用查询两个日期内的数据,如下图所示

 strSQL = "SELECT * From [Sheet1$A:C] where 日期>=#" & Range("J1") & "# And 日期<=#" & Range("K1") & "#"

like关键字

Like 操作符用于在 WHERE 子句中搜索列中的模糊匹配

Select 列字段名 From [工作表名$] Where 列字段 Like '关键字'

可以加上通配符% （相当于函数公式中的通配符*）

strSQL = "Select * from [Sheet1$] Where 姓名 like '[AB]%'"

这一语句表示匹配姓名以A开头或B开头的数据
同样的可以反向查询姓名不以A开头或B开头的数据

strSQL = "Select * from [Sheet1$] Where 姓名 like '[!AB]%'"
--or
strSQL = "Select * from [Sheet1$] Where not 姓名 like '[AB]%'"

in关键字

In运算符允许在 WHERE 子句中规定多个值,语法如下：

Select 字段 From [表名$] Where 字段 In(条件1, 条件2,条件3, ....)

如果在In的条件中不是数值类型，一定要加上引号。
eg:

Select *  FROM [Sheet1$A:D] where 省份 in('广东','广西')

2.两表的上下拼接

将两表连接起来的语句一般是UNION和UNION ALL
UNION ALL是查询所有记录（直接连接，可以重复），UNION只查询不重复（指整条记录不重复，取唯一）的记录，两种语句如果有重复记录，则查询结果就不一样。
在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
UNION在运行时先取出几个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。
例如将sheet1与sheet2中的数据连接起来：

SELECT 日期,姓名,产品数 From [Sheet1$] UNION SELECT 日期,姓名,产品数 From [Sheet2$]

这个是在两张表的格式一样的情况下直接连接，但是有些情况下两表的格式并不一样，这个时候可以使用下列语句：
例如sheet1中有日期，姓名两个字段，而sheet2中有姓名，产品数两个字段，需要上下连接两个表格：

SELECT 日期,姓名,null as 产品数 From [Sheet1$] UNION SELECT null as 日期,姓名,产品数 From [Sheet2$]

即使用null加列拼成格式相同的两个表格再连接

3.两表的左右拼接

两表的左右拼接即是join关键字
SQL代替Vlookup 精确查找（左连接 “Left Outer JOIN” 用法），语法结构为：

Select 表名 .字段1,表名 .字段2,表名 .字段3,表名 .字段4 FROM 查询表 AS 别名1  Left Outer JOIN 被查询表 AS 别名2 ON  别名1.字段名=别名2.字段名

例如：

SELECT A.姓名,B.性别,B.部门 FROM [Sheet2$] AS A Left Outer JOIN [Sheet1$] AS B ON A.姓名=B.姓名
--简写为
SELECT A.姓名,性别,部门 FROM [Sheet2$]A Left JOIN [Sheet1$]B ON A.姓名=B.姓名

join除了left join外还有right join，inner join等，之间的区别与sql语句都是一致的。注意VBA中的SQL是不支持outer join连接的

4.三表连接

注意语句中的括号，在VBA SQL中不使用括号会导致错误

select b.Impression,b.Click,c.Visits,c.DemandClick,c.Bounce_Rate,c.PD_Page_View,c.Add_to_Cart,c.Cart_Additions,c.My_Account_Registration,c.Average_Time_Spent,c.Checkout_Starts,c.Total_Orders from ([database template$] as a left JOIN [Raw$] as b on b.SPID=a.placement_id) left JOIN [Raw_Om$] as c on c.key=a.key"

3.数据透视表

TRANSFORM语句可以实现像透视表一样的显示结果，语法结构为：

TRANSFORM 汇总方式  SELECT 行标签 from 表名 group by 行字段 PIVOT 列标签

这一语句是非常常用的方法，要注意TRANSFORM后的汇总方式为一个聚合函数(一般为sum),同时后续的SELECT语句中不要加入a字段/b字段或者having语句，否则会导致语句错误无法运行
eg:

TRANSFORM Sum(数量) Select 产品名称 from [Sheet1$] group by 产品名称 PIVOT 款号

TRANSFORM语句话还可以加入一些自定义的设置：

TRANSFORM 汇总方式  SELECT 行标签 from 表名 group by 行字段 PIVOT 列标签 in [列标签值1，列标签值2，列标签值3.....]

使用in可以限制列标签的内容，同时可以规定列字段值的展示顺序

4.一些其他SQL语句的补充

在SQL中使用广泛的case关键字在VBA　SQL中的实现方式为IIF，IIF的语法为：
IIf(Logical EXPression, Numeric Expression1, Numeric Expression2)
如果 Logical Expression 取值为 TRUE，则此函数返回 Numeric Expression1，否则，返回 Numeric Expression2。

例如：
当site列填NA时，将if_address设为N，否则设为Y

select name,iif([site]='NA','N','Y') as if_address form [student$]

同理，Logical Expression可以使用isnull,数字等值判断语句等

同时iif语句对符合条件的数据进行计算。例如如果是赠送的，实际的花费为0,否则为原价购买计算花费

select 客户,sum(iif(buying_type='赠送',price,0)) as cost  from  [order$]

直接从其他文件获取数据的SQL语句：

Select * From [盘符:\路径\工作簿名1.后辍名].[工作表名$] Union All Select * From [盘符:\路径\工作簿名2.后辍名].[工作表名$] Union All Select * From [盘符:\路径\工作簿名3.后辍名].[工作表名$]