他山之石——VBA SQL基础

这位“兰色幻想”老师还真是苦口婆心，在教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