关于sql里,交叉表, TRANSFORM函数和ADODB.Recordset的用法

鑫尼达

业务中,有时候遇到这种需求情况:
原始数据表样式如下图:
数据表:


最终需要生成的报表如下图:
报表:


目前我的实现思路是:

前面ado连接等部分省略

'第一步分别写出每个指标的交叉表查询语句

指标1sql = " TRANSFORM SUM([指标1]) SELECT [产品1] FROM 数据表 GROUP BY [产品1] PIVOT [店铺名] "
指标2sql = " TRANSFORM SUM([指标2]) SELECT [产品1] FROM 数据表 GROUP BY [产品2] PIVOT [店铺名] "
指标3sql = " TRANSFORM SUM([指标3]) SELECT [产品1] FROM 数据表 GROUP BY [产品3] PIVOT [店铺名] "

'第二步执行sql语句.
Set Rst1 = conn.Execute(指标1sql)
Set Rst2 = conn.Execute(指标2sql)
Set Rst3 = conn.Execute(指标3sql)

'第三步,每个指标执行sql语句得到的数据集结果复制到报表.用最大行数来控制复制到报表的行数.部分代码省略.
i = Sheets("报表").Cells(Rows.Count, 2).End(xlUp).Row + 1
Set Rst2 = conn.Execute(指标2sql)
Sheets("报表").Cells(i, "a").CopyFromRecordset Rst2


目前已经用这个思路可以实现需求.
然后我有个疑问:
这种办法,有几个指标,就要执行几次sql查询,还要执行几次写入工作表.目前来看,运行效率相对来说还有点不满意.想看看还有没有优化的可能.

已知,带 TRANSFORM 函数的 sql语句,没法作为子查询,所以就没法把三个子查询 union all 起来做到只执行一次sql查询只执行一次写入工作表.

那么,除此之外,还有别的更快的实现办法吗?

然后,关于,ADODB.Recordset的用法,
能否把执行sql语句后的结果 数据集, Rst1,Rst2,Rst3,先"union all "起来,然后再一次性写入到工作表呢?这样总能减少几次写入工作表的操作.

另外还有一个小小的疑问:
经常看到别的大神的ado查询的代码里,有类似这样的语句.
Rst.Open sq, conn, 1, 1
这句具体是什么意思呢?后面的参数是什么意思起什么作用呢?

由于这个只是思路和语法上的疑问,就不上传附件了.

初学者目前还只会套用一些代码.很多基础知识语法什么的都不懂.感谢论坛各位前辈们指点迷津.

鑫尼达

下班前顶一下,跪求大佬们指导一下

zpy2

鑫尼达 发表于 2021-8-4 17:38
下班前顶一下,跪求大佬们指导一下

https://club.excelhome.net/forum.php?mod=viewthread&tid=1581241&mobile=&_dsign=4dcc3db9
目测，数据表要转换一下为 一维表 后才能 trans?

另外，合并单元格 参考 上面，你的sql貌似不支持？

鑫尼达

zpy2 发表于 2021-8-4 17:58
https://club.excelhome.net/forum.php?mod=viewthread&tid=1581241&mobile=&_dsign=4dcc3db9
目测，数 ...

现在这样的数据表不是一维表吗
合并单元格我是后面再用循环合并的。
这不是重点啊。我已经实现了取数和排序 然后合并单元格。
主要是看能不能在内存里就把数据集合并起来再一次性写入工作表