【原】Excel VBA【案例另解：交叉表查询】库存汇总：根据物料编码和仓库代码自动抓取库存

冷茶视界 2023-11-15 发布于江苏

展开全文

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】【2023年8月】【2023年9月】【2023年10月】

实用案例

|日期控件 ||简单的收发存||收费管理系(Access改进版)|

|电子发票管理助手||电子发票登记系统(Access版)|

|文件合并 ||表格拆分 ||审计凭证抽查底稿|

|中医诊所收费系统(Excel版)||中医诊所收费系统(Access版)||银行对账单自动勾对|

收费使用项目

|财务管理系统||工资薪金和年终奖个税筹划|

内容提要

SQL交叉表查询

大家好，我是冷水泡茶，前天我们分享了一个案例【库存汇总：根据物料编码和仓库代码自动抓取库存】，后来有人留言说，

SQL，transform语句，速度快

当时我也没有回复，原因是Transform我没怎么用过（哎呀，就是不会嘛）。不过，他的留言倒是让我想起另外一件事，在我的《财务管理系统》中有一张报表，叫“分月损益对比表”，长这样子：

但是我没有用transform ，我是把每个月的数据查询出来再拼到一起的，感觉有点low，实际在运行的时候速度就是有点慢。要是能用Transform来一把头查询数据，那岂不是爽歪歪？

于是乎就上网搜了搜，（不懂的就上网搜，这是个好习惯）就拿前天的案例【库存汇总】来测试吧，结果是可行的，而且相当丝滑：

两种方法我们都给它运行一下，结果是一致的，区别在于，在SQL方法下，表头字段是排了序的。

在速度方面，看不出有太大区别，这点不重要，我们只要掌握方法与思路就好，在实际工作中择优选用。我想，如果数据量特别大，SQL应该会快一点。

在代码量方面，SQL方法少了很多。

基本思路：

1、创建ADODB连接。

2、编写交叉表查询SQL语句。

3、执行查询。

4、把记录集结果存到工作表。

VBA代码：

Sub sqlTransform()    Dim ws As Worksheet    Dim cnn As Object, rs As Object    Dim strCnn As String, dbs As String, sql As String, tbl As String    dbs = ThisWorkbook.FullName    tbl = "[B表$]"    Set cnn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " _        & dbs & ";Extended Properties='Excel 12.0 Xml;HDR=YES';"    cnn.Open strCnn    sql = "TRANSFORM sum(`可用量(主单位)`) " _        & "SELECT 物料编码,物料名称,sum(`可用量(主单位)`) " _        & "as 合计库存 FROM " & tbl _        & " Group BY 物料编码,物料名称 PIVOT 仓库名称"    rs.Open sql, cnn    Set ws = Sheet2    With ws        .Cells.Clear        For i = 1 To rs.Fields.Count            .Cells(1, i) = rs.Fields(i - 1).Name        Next        .Range("A2").CopyFromRecordset rs    End With    rs.Close    cnn.CloseEnd Sub

代码解析：

1、Line2~4，定义变量。

2、Line5，当前工作簿作为数据库文件，把完整文件路径赋值给字符串dbs。

3、Line6，定义待查询的工作表tbl。

4、Line9，设置连接字符串。

5、Line11，打开连接。

6、Line12，交叉表查询SQL语句。基本结构是：“Transform sum(汇总字段)+SQL+Pivot 分组字段”。这里它的汇总字段是“可用量(主单位)”，它带有括号，直接写是会出错的，前后要加上反引号“`”，或者把字段名改为“可用量”或者“数量”之类不含特殊字段的字段。

7、Line16，打开记录集对象。

8、Line20~22，在目标工作表中填写表头字段。

9、Line23，把记录集内容复制到工作表。

后记

1、当我把这个库存汇总搞定之后，我就动手去改开头提到的分月损益表，可惜的是，Transform交叉表查询是Access数据库独有的，在MySQL数据库中用不起来。

2、在MySQL中我们只能另辟蹊径，通过循环月份，把月份字段里的月份值作为SQL语句的字段，这里截取一段，主要看temsql拼接SQL查询语句的方法，For循环里，就是循环月份数组arrMonth，把它的值一个一个拼接成SQL查询语句：

temSql = "select 科目代码,科目全称,"For i = 0 To UBound(arrMonth, 2)    temSql = temSql & "SUM(CASE WHEN 月份 = '" & arrMonth(0, i) & "' THEN 金额 ELSE 0 END) AS '" & arrMonth(0, i) & "', "NexttemSql = Left(temSql, Len(temSql) - 2)sql = temSql & " from (" & sql & ") as sq group by 科目代码,科目全称"

最后一条查询语句，也是最终的结果，它的完整字符是这样的：

select 科目代码,科目全称,SUM(CASE WHEN 月份 = '202301' THEN 金额 ELSE 0 END) AS '202301', SUM(CASE WHEN 月份 = '202302' THEN 金额 ELSE 0 END) AS '202302', SUM(CASE WHEN 月份 = '202303' THEN 金额 ELSE 0 END) AS '202303', SUM(CASE WHEN 月份 = '202304' THEN 金额 ELSE 0 END) AS '202304', SUM(CASE WHEN 月份 = '202305' THEN 金额 ELSE 0 END) AS '202305', SUM(CASE WHEN 月份 = '202306' THEN 金额 ELSE 0 END) AS '202306' from (select 科目代码,科目全称,月份, if(方向='借',借方金额,贷方金额) as 金额 from (select a.*, b.方向 from (select 科目代码,科目全称,月份,sum(借方金额) as 借方金额,sum(贷方金额) as 贷方金额 from tb_voucher where 科目代码 in (select 科目代码 from (select distinct 科目代码,科目全称 from tb_voucher where 作废标志=False and 科目代码 in (select 科目代码 from tb_account where 科目分类='损益类') order by 科目代码) as sa) group by 科目代码,科目全称,月份) as a left join tb_account as b on a.科目代码=b.科目代码) as s2) as sq group by 科目代码,科目全称

别看这个SQL语句这么长，它其实是一条一条SQL语句嵌套拼接起来的，实际上也没有多复杂。

好，今天就到这，我们下期再会。

~~~~~~End~~~~~~

	安利小店安利的牙膏非常不错，用了以后就不想再用其他的了；洗洁精、洗衣液也是日常必备，用过都说好！
	合谷医疗合谷医疗专攻各种疑难杂症，尤其擅长腰颈椎疾病、儿童神经发育异常、多动症、自闭孤独症治疗，可谓神乎其技！体验过的直呼早点来就好了！