VBA入门到进阶常用知识代码总结72

第72集 SQL语言基础
354、 按条件筛选

类模块中：
Property Get Excel数据库()
Excel数据库 = “provider= Microsoft.ACE.OLEDB.12.0;extended properties=excel 8.0;data source=” & ThisWorkbook.path & “/Database/CangKu.xls”
End Property

Property Get Access数据库()
Access数据库 = “provider= Microsoft.ACE.OLEDB.12.0;data source=” & ThisWorkbook.path & “/Database/CangKu.mdb”
End Property

Sub 执行筛选(Mydata As String, sq As String)
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. 按条件筛选—数字条件
   '直接用对比符号,><=
   Sub 按条件筛选1()
   Dim sql As String
   Dim data As New 数据库
   sql = “Select * from [sheet1$] Where 销售单价 > 100”
   data.执行筛选 data.Excel数据库, sql
   End Sub

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

3. 按条件筛选—日期条件
   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. 按条件筛选—区间条件
   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连接
   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. 按条件筛选–模糊条件
   '%表示任意多个字符,_(下划线)表示单个占位符
   Sub 按条件筛选6()
   Dim sql As String
   Dim data As New 数据库
   sql = “Select * from [sheet1$] Where 物品名称 like ‘%扶手%’”
   data.执行筛选 data.Excel数据库, sql
   End Sub

7. 按条件筛选–插入变量
   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. 按条件筛选–在字符串组里
   Sub 按条件筛选8()
   Dim sql As String
   Dim data As New 数据库
   sql = “Select * from [sheet1$] Where 物品名称 in(‘车衣’,‘扶手箱’)”
   data.执行筛选 data.Excel数据库, sql
   End Sub

9. 按条件筛选–借用函数
   Sub 按条件筛选9()
   Dim sql As String
   Dim data As New 数据库
   sql = “Select * from [sheet1$] Where left(物品代码,3)=‘028’”
   data.执行筛选 data.Excel数据库, sql
   End Sub
   355、 筛选方式
   1 筛选全部字段
   *表示全部字段
   Sub 筛选全部字段()
   Dim sql As String
   Dim data As New 数据库
   sql = “Select * from ChuKu”
   data.执行筛选 data.Access数据库, sql
   End Sub

2 筛选指定字段
'在from前面列出要显示的所有字段,如果要跳过的用""""
Sub 显示指定字段()
Dim sql As String
Dim data As New 数据库
sql = “Select “””",出库日期,"""",物品代码,"""",规格,单位 from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

3 筛选不重复的
Distinct 字段名 筛选不重复的记录
Sub 不重复筛选()
Dim sql As String
Dim data As New 数据库
sql = “Select Distinct “””",物品代码 from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

4 筛选前N个
TOP N 只显示前N个记录
Sub 筛选前10个() '按个数筛选
Dim sql As String
Dim data As New 数据库
sql = “Select top 10 * from ChuKu”
data.执行筛选 data.Access数据库, sql
End Sub

Top N percent 可以显示前百分之N的记录
Sub 筛选百分之N() '按百分比筛选
Dim sql As String
Dim data As New 数据库
sql = “Select top 30 Percent * from ChuKu”
data.执行筛选 data.Access数据库, sql
End Sub

5 格式化显示的结果
可以对筛选的字段用函数进行进一步的处理和格式化
Sub 格式化字段() '按百分比筛选
Dim sql As String
Dim data As New 数据库
sql = “Select ID,Format(出库日期,”“yyyy-mm-dd”") from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

6 对筛选后的结果排序
Sub 排序() ’
'Desc降序
'Asc升序
Dim sql As String
Dim data As New 数据库
sql = “Select * from ChuKu Order by 出库日期 asc,销售单价 desc”
data.执行筛选 data.Access数据库, sql
End Sub
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 可以配合函数进行分组求和,分组求最大值等.
Sub 分组() ’
Dim sql As String
Dim data As New 数据库
sql = “Select “””","""",物品代码,"""","""","""",sum(出库数量) from ChuKu group by 物品代码"
data.执行筛选 data.Access数据库, sql
End Sub
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
356、 SQL函数应用
1 SUM函数求和,count计数
Sub 求和()
Dim sql As String
Dim data As New 数据库
sql = “Select sum(出库数量),count(出库数量) from ChuKu where 物品代码=‘0270001’”
data.执行筛选 data.Access数据库, sql
End Sub

2 left,right,mid,instr,format文本函数
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日期函数
Sub 日期()
Dim sql As String
Dim data As New 数据库
sql = “Select 出库日期, year(出库日期),month(出库日期),day(出库日期),DateDiff(”“m”",出库日期,now) from ChuKu"
data.执行筛选 data.Access数据库, sql
End Sub

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

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