看似没用的东西,在真正需要的时候就会很珍贵。书到用时方恨少,技到用时方恨无啊!所以,在技术面前,要保持一颗敬畏的心。
- 'Union (AlL) 多个select查询结果合并在一起
- Sub 合并工作表数据()
- Dim data As New 类1
- Dim sql As String
- sql = "select * from [Sheet1$a:c] union all select * from [sheet2$a:c]"
- data.执行筛选 sql, "a2"
- End Sub
- Sub 合并工作表数据2()
- Dim data As New 类1
- Dim sql As String
- sql = "select * from [Sheet1$a:c] union select * from [sheet2$a:c]"
- data.执行筛选 sql, "a2"
- End Sub
- '查找两个表中相同的
- 'Select 字段 from 表1,表2 where 表1.字段=表2.字段
- Sub 列出相同()
- Dim data As New 类1
- Dim sql As String
- sql = "select [Sheet1$a:c].* from [Sheet1$a:c],[Sheet2$a:c] where [Sheet1$a:c].类别=[Sheet2$a:c].类别"
- data.执行筛选 sql, "a2"
- End Sub
- 'Select 字段 from 表1 Inner Join 表2 on 条件
- Sub 列出相同2()
- Dim data As New 类1
- Dim sql As String
- sql = "select [Sheet1$].*,[sheet2$].库别 from [Sheet1$] Inner Join [sheet2$] on [Sheet1$].类别=[sheet2$].类别"
- data.执行筛选 sql, "a2"
- End Sub
- '两表汇总
- Sub 汇总()
- Dim data As New 类1
- Dim sql As String
- Dim sq As String
- sql = "select * from [sheet1$a:c] union all select * from [sheet2$a:c]"
- sq = "select 类别,sum(数量),sum(金额) from (" & sql & ") group by 类别"
- data.执行筛选 sq, "a2"
- End Sub
- '
- Sub 合并()
- Dim data As New 类1
- Dim sql As String
- sql = "select [Sheet1$].*,[sheet2$].库别 from [Sheet1$] left Join [sheet2$] on [Sheet1$].类别=[sheet2$].类别"
- data.执行筛选 sql, "a2"
- End Sub
- 'JOIN: 如果表中有至少一个匹配,则返回行
- 'LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
- 'RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- 'FULL JOIN: 只要其中一个表中存在匹配,就返回行,可惜的是在EXCEL VBA中不支持
- Sub 执行筛选(sq, Rg As String)
- Dim conn As New Connection
- With ActiveSheet 'Sheets("sheet1")
- .Range(Rg).Resize(100, 7) = ""
- conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- .Range(Rg).CopyFromRecordset conn.Execute(sq)
- End With
- conn.Close
- Set conn = Nothing
- End Sub