VBA学习笔记70: Select语句基础

2 年前 · 来自专栏 VBA学习笔记（完结）

夏西蓝

不用关注我哈，不会再更VBA的内容了

学习资源：《Excel VBA从入门到进阶》第72集 by兰色幻想

这节课来详细讲解Select语句。

Select 字段 from 表 where 条件

例：从sheet1中筛选全部数据。

* 表示全部字符，无条件可以省略where。

Select * from [sheet1$]

如果是对表中特定单元格区域进行查找，可以在[sheet1$]的$后面加单元格区域。

在开讲之前，先在类模块写好打开数据库、复制筛选后的数据到单元格区域和关闭数据库的代码，以便后续可直接调用和增加代码可读性。

类模块命名为数据库，三个属性的名称分别为Excel数据库、Access数据库、执行筛选。

Property Get Excel数据库()
Excel数据库 = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.path & "/Database/CangKu.xls"
End Property
Property Get Access数据库()
Access数据库 = "provider=Microsoft.jet.OLEDB.4.0;data source=" & ThisWorkbook.path & "/Database/CangKu.mdb"
End Property
Sub 执行筛选(Mydata As String, sq As String)   '参数有数据库Mydata和地址sq
Dim conn As New Connection
Dim rst As New Recordset
With ActiveSheet 'Sheets("sheet1")
    .Range("a2:i100") = ""     '清空单元格区域
    conn.Open Mydata         '打开数据库
    .Range("a2").CopyFromRecordset conn.Execute(sq)  '把数据复制到单元格中
End With
conn.Close              '关闭数据库
Set conn = Nothing '释放对象
End Sub

本节课所用的数据库是

一、按条件筛选

按条件筛选——数字条件

直接用对比符号,><=

例1：在sheet1中筛选销售单价>100的数据。

Sub 按条件筛选1()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 销售单价 > 100"
data.执行筛选 data.Excel数据库, sql
End Sub

2. 按条件筛选——字符条件

注意：字符条件是文本字符要用单引号括起来。

例2：在sheet1中筛选物品名称为'挡泥板'的数据。

Sub 按条件筛选2()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 ='挡泥板'"
data.执行筛选 data.Excel数据库, sql
End Sub

3. 按条件筛选——日期条件

日期条件前后要加#号。

例3：在sheet1中筛选出库日期是2005-1-4的数据。

Sub 按条件筛选3()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 出库日期 =#2005-1-4#"
data.执行筛选 data.Excel数据库, sql
End Sub

4. 按条件筛选——区间条件

between 条件1 and 条件2

例4：在sheet1中筛选出库日期在2005-1-4至2005-1-10之间的数据。

Sub 按条件筛选4()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 出库日期 between  #2005-1-4# and #2005-1-10#"
data.执行筛选 data.Excel数据库, sql
End Sub

5. 按条件筛选——多个条件

用and、OR连接

例5：在sheet1中筛选出库日期在2005-1-4至2005-1-10之间且销售单价大于100的数据。

Sub 按条件筛选5()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 出库日期 between  #2005-1-4# and #2005-1-10# and 销售单价>100"
data.执行筛选 data.Excel数据库, sql
End Sub

6. 按条件筛选——模糊条件

%表示任意多个字符，_(下划线)表示单个占位符

例6：在sheet1中筛选出物品名称含扶手的数据。

Sub 按条件筛选6()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 like '%扶手%'"
data.执行筛选 data.Excel数据库, sql
End Sub

7. 按条件筛选——插入变量

注意：变量也要用单引号括起来。

例7：在sheet1中筛选出物品名称为挡泥板的数据。

Sub 按条件筛选7()
Dim sql As String
Dim data As New 数据库
Dim sr
sr = "挡泥板"
sql = "Select * from [sheet1$] Where 物品名称 ='" & sr & "'"
data.执行筛选 data.Excel数据库, sql
End Sub

8. 按条件筛选——在字符串组里

in('字符1','字符2',……)

例8：在sheet1中筛选出物品名称为车衣和扶手箱的数据。

Sub 按条件筛选8()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 in('车衣','扶手箱')"
data.执行筛选 data.Excel数据库, sql
End Sub

9. 按条件筛选——借用函数

例9：在sheet1中筛选出物品代码前三位是028的数据。

Sub 按条件筛选9()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where left(物品代码,3)='028'"
data.执行筛选 data.Excel数据库, sql
End Sub

二、筛选方式

筛选方式是指结果的样式。

1. 筛选全部字段

*表示全部字段

例10：在ChuKu中筛选全部数据。

Sub 筛选全部字段()
Dim sql As String
Dim data As New 数据库
sql = "Select * from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

2. 筛选指定字段

在from前面列出要显示的所有字段,如果要跳过的用"""" （两对双引号），中间那一对是系统要识别的，双引号里面没有东西表示是空值，外面那对双引号的因为里面的双引号不是字段名，所以要加上双引号。

例11：在ChuKu中筛选指定字符：出库日期、物品名称、单位。

Sub 显示指定字段()
Dim sql As String
Dim data As New 数据库
sql = "Select """",出库日期,"""",物品名称,"""",单位 from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

3. 筛选不重复的

Distinct 字段名 '筛选不重复的记录

例12：在ChuKu中对物品代码列进行筛选不重复的数据。

Sub 不重复筛选()
Dim sql As String
Dim data As New 数据库
sql = "Select Distinct """",物品代码 from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

4. 筛选前N个

① 只显示前N个记录

  TOP N

例13：筛选ChuKu前10个数据。

Sub 筛选前10个()      '按个数筛选
Dim sql As String
Dim data As New 数据库
sql = "Select top 10 * from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

② 显示前百分之N的记录

Top N percent

例14：筛选ChuKu前10%的数据。

Sub 筛选百分之N() '按百分比筛选
Dim sql As String
Dim data As New 数据库
sql = "Select top 30 Percent * from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

5. 格式化显示的结果

对筛选的字段用函数进行进一步的处理和格式化。

例15：在ChuKu中筛选指定字符：ID和出库日期，而且出库日期的格式设置为 yyyy-mm-dd 。

Sub 格式化字段()
Dim sql As String
Dim data As New 数据库
sql = "Select  ID,Format(出库日期,""yyyy-mm-dd"") from ChuKu" 
'yyyy-mm-dd是文本字符串，需要加两对双引号
data.执行筛选 data.Access数据库, sql
End Sub

6. 对筛选后的结果排序

Desc 降序
Asc 升序

如果写两个排序条件的话，按写的顺序觉得排序条件优先级。

例16：在ChuKu中筛选全部字符，且按出库日期升序，按销售日期降序排列。

Sub 排序() '
Dim sql As String
Dim data As New 数据库
sql = "Select  * from ChuKu Order by 出库日期 asc,销售单价 desc"
data.执行筛选 data.Access数据库, sql
End Sub

例17：在ChuKu中先按出库日期升序，按销售单价降序排列，然后筛选出前十个数据。

Sub 筛选销售数量前10() '
Dim sql As String
Dim data As New 数据库
sql = "Select  Top 10 * from ChuKu Order by 出库日期 asc,销售单价 desc"
data.执行筛选 data.Access数据库, sql
End Sub

7. 分组显示

Group by 可以配合函数进行分组求和,分组求最大值等.

例18：在ChuKu中对相同的物品代码的出库数量进行求和。

Sub 分组() 
Dim sql As String
Dim data As New 数据库
sql = "Select  """","""",物品代码,"""","""","""",sum(出库数量) from ChuKu group by 物品代码"
data.执行筛选 data.Access数据库, sql
End Sub

例19：在ChuKu中对相同的物品代码的出库数量进行求和，并只显示出库数量合计大于3的数据。

Sub 按条件显示分组记录() 
Dim sql As String
Dim data As New 数据库
sql = "Select  """","""",物品代码,"""","""","""",sum(出库数量) from ChuKu group by 物品代码 HAVING sum(出库数量)>=3"
data.执行筛选 data.Access数据库, sql
End Sub

三、SQL函数应用

1. Sum函数求和，Count计数

例20：在ChuKu中筛选出物品代码是0270001的，并对其ID计数，出库数量求和。

Sub 求和()
Dim sql As String
Dim data As New 数据库
sql = "Select count(ID),"""","""","""","""","""",sum(出库数量) from ChuKu where 物品代码='0270001'"
data.执行筛选 data.Access数据库, sql
End Sub

2. left,right,mid,instr,format文本函数

例21：在ChuKu中筛选出物品代码，并展示它的前三位数字，后四位数字，顺数第二位开始的两位数，物品代码中“1”所在的位置。

Sub 文本()
Dim sql As String
Dim data As New 数据库
sql = "Select 物品代码, ""左三位:"" & left(物品代码,3),right(物品代码,4),mid(物品代码,2,2),instr(物品代码,""1"") from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

3. year,month,day,datediff,DateSerial日期函数

datediff VBA函数，返回两个日期之间的时间。
DateSerial 可以把指定的年、月、日合并为日期。

例22：在ChuKu中筛选出出库日期，并展示它的年份，月份，日期，出库日期与当前日期的年份差。

Sub 日期()
Dim sql As String
Dim data As New 数据库
sql = "Select 出库日期, year(出库日期),month(出库日期),day(出库日期),DateDiff(""yyyy"",出库日期,now) from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

4. max,min,first,last 最值函数

first 筛选到的第一条记录
last 筛选到的最后一条记录

例23：在ChuKu中筛选物品名称，并展示它的最小销售金额。

Sub 最小值()
Dim sql As String
Dim data As New 数据库
sql = "Select 物品名称,min(销售金额) from ChuKu group by 物品名称"
data.执行筛选 data.Access数据库, sql
End Sub

例24：在ChuKu中筛选物品名称，并展示它的最后一条销售金额。

Sub 最新值()
Dim sql As String