这位“兰色幻想”老师还真是苦口婆心,在教VBA的过程中,连SQL也都附带教一遍,很有心!
'按条件筛选---数字条件
'直接用对比符号,><=
Sub 按条件筛选1()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 销售单价 > 100"
data.执行筛选 data.Excel数据库, sql
End Sub
'按条件筛选---字符条件
Sub 按条件筛选2()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 ='挡泥板'"
data.执行筛选 data.Excel数据库, sql
End Sub
'按条件筛选---日期条件
Sub 按条件筛选3()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 出库日期 =#2005-1-4#"
data.执行筛选 data.Excel数据库, sql
End Sub
'按条件筛选---区间条件
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
'按条件筛选--多个条件
'用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
''按条件筛选--模糊条件
'%表示任意多个字符,_(下划线)表示单个占位符
Sub 按条件筛选6()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 like '%扶手%'"
data.执行筛选 data.Excel数据库, sql
End Sub
''按条件筛选--插入变量
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
''按条件筛选--在字符串组里
Sub 按条件筛选8()
Dim sql As String
Dim data As New 数据库
sql = "Select * from [sheet1$] Where 物品名称 in('车衣','扶手箱')"
data.执行筛选 data.Excel数据库, sql
End Sub
''按条件筛选--借用函数
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 筛选全部字段
'*表示全部字段
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
'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