ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 1673|回复: 11

[已解决] 我春节期间刚学SQL,请问大神们以下SQL代码如何提效?

[复制链接]

TA的精华主题

TA的得分主题

发表于 2021-3-1 18:36 | 显示全部楼层 |阅读模式
本帖最后由 吾股丰登 于 2021-3-2 17:14 编辑
  1. Sub sql()
  2.     Dim cnn As Object
  3.     Dim rst As Object
  4.     Dim sql As String
  5.     Set cnn = CreateObject("ADODB.Connection")
  6.     Set rst = CreateObject("ADODB.Recordset")
  7.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  8.         "Data Source=" & ThisWorkbook.FullName & ";" & _
  9.         "Extended Properties=Excel 12.0;"
  10.     '----<font color="#2e8b57">请问以下这个sql如何优化?因为实际业务中,如果表“查询a”或“任务d”或“销量e”的数据量哪怕才几百上千行,那这代码的效率就几何下降了,谢谢。</font>
  11. <font color="#ff0000">    sql = "select a.客户编码,b.客户名称,c.余额," _
  12.         & "(select sum(任务) from [任务d$]d where d.客户编码=a.客户编码 and 月份=1) as '任务1月'," _
  13.         & "(select sum(任务) from [任务d$]d where d.客户编码=a.客户编码 and 月份=2) as '任务2月'," _
  14.         & "(select sum(销量) from [销量e$]e where e.客户编码=a.客户编码 and 月份=1) as '销量1月'," _
  15.         & "(select sum(销量) from [销量e$]e where e.客户编码=a.客户编码 and 月份=2) as '销量2月'" _
  16.         & "from (SELECT * FROM [查询a$A2:A5]a left join  [名称b$]b on a.客户编码=b.客户编码)a1  " _
  17.         & "left join  [余额c$]c on a1.a.客户编码=c.客户编码"</font>
  18.     '----<font color="#2e8b57">请问以上这个sql如何优化?因为实际业务中,如果表“查询a”或“任务d”或“销量e”的数据量哪怕才几百上千行,那这代码的效率就几何下降了,谢谢。</font>
  19.     Set rst = cnn.Execute(sql)
  20.     With Sheets("查询a")
  21.         .Range("11:10000").ClearContents
  22.         For i = 0 To rst.Fields.Count - 1
  23.             .Cells(11, i + 1) = rst.Fields(i).Name
  24.         Next
  25.         .Range("A12").CopyFromRecordset rst
  26.     End With
  27.    
  28.     cnn.Close
  29.     Set rst = Nothing
  30.     Set cnn = Nothing
  31. End Sub

复制代码


请教取数20200301.rar

23.2 KB, 下载次数: 14

SQL提效

TA的精华主题

TA的得分主题

发表于 2021-3-2 07:42 来自手机 | 显示全部楼层
子查询,和连接查询的确比较慢。
基本的添加索引试试吧。

TA的精华主题

TA的得分主题

发表于 2021-3-2 08:10 | 显示全部楼层
zpy2 发表于 2021-3-2 07:42
子查询,和连接查询的确比较慢。
基本的添加索引试试吧。

EXCEL 如何设置所索引

TA的精华主题

TA的得分主题

发表于 2021-3-2 08:16 来自手机 | 显示全部楼层
hcunicom 发表于 2021-3-2 08:10
EXCEL 如何设置所索引

excel不能,只能是数据库,比如 sqlite

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-3-2 10:27 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
zpy2 发表于 2021-3-2 08:16
excel不能,只能是数据库,比如 sqlite

谢谢大佬的热心解答。

TA的精华主题

TA的得分主题

发表于 2021-3-2 12:24 | 显示全部楼层
不知道是否能提高效率

  1. sql = ""
  2.     sql = sql & "select 客户编码"
  3.     sql = sql & ",MAX(客户名称) AS 客户名称"
  4.     sql = sql & ",SUM([余额]) AS [余额]"
  5.     sql = sql & ",SUM([任务1月]) AS [任务1月]"
  6.     sql = sql & ",SUM([任务2月]) AS [任务2月]"
  7.     sql = sql & ",SUM([销量1月])  AS [销量1月]"
  8.     sql = sql & ",SUM([销量2月]) AS [销量2月]"
  9.    
  10.     sql = sql & " from ("
  11.    
  12.     sql = sql & "select 客户编码,NULL AS 客户名称"
  13.     sql = sql & ",IIF(月份=1,任务,0) AS [任务1月]"
  14.     sql = sql & ",IIF(月份=2,任务,0) AS [任务2月]"
  15.     sql = sql & ",0 AS [销量1月]"
  16.     sql = sql & ",0 AS [销量2月]"
  17.     sql = sql & ",0 AS [余额]"
  18.     sql = sql & " from [任务d$]"
  19.    
  20.     sql = sql & " UNION ALL "
  21.     sql = sql & "select 客户编码,NULL AS 客户名称"
  22.     sql = sql & ",0 AS [任务1月]"
  23.     sql = sql & ",0 AS [任务2月]"
  24.     sql = sql & ",IIF(月份=1,销量,0) AS [销量1月]"
  25.     sql = sql & ",IIF(月份=2,销量,0) AS [销量2月]"
  26.     sql = sql & ",0 AS [余额]"
  27.     sql = sql & " from [销量e$]"
  28.    
  29.     sql = sql & " UNION ALL "
  30.     sql = sql & "select 客户编码,NULL AS 客户名称"
  31.     sql = sql & ",0 AS [任务1月]"
  32.     sql = sql & ",0 AS [任务2月]"
  33.     sql = sql & ",0 AS [销量1月]"
  34.     sql = sql & ",0 AS [销量2月]"
  35.     sql = sql & ",[余额]"
  36.     sql = sql & " from [余额c$]"
  37.    
  38.     sql = sql & " UNION ALL "
  39.     sql = sql & "select 客户编码,客户名称"
  40.     sql = sql & ",0 AS [任务1月]"
  41.     sql = sql & ",0 AS [任务2月]"
  42.     sql = sql & ",0 AS [销量1月]"
  43.     sql = sql & ",0 AS [销量2月]"
  44.     sql = sql & ",0 AS [余额]"
  45.     sql = sql & " from [名称b$]"
  46.    
  47.     sql = sql & ") WHERE 客户编码 IN ("
  48.     sql = sql & "SELECT 客户编码 FROM [查询a$A2:A5]"
  49.     sql = sql & ")"
  50.     sql = sql & " GROUP BY 客户编码 "
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2021-3-2 13:28 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
zpy2 发表于 2021-3-2 08:16
excel不能,只能是数据库,比如 sqlite

sqlite中文怎么转码啊,有方案或者教程吗?

TA的精华主题

TA的得分主题

发表于 2021-3-2 13:32 来自手机 | 显示全部楼层
语虚何以言知 发表于 2021-3-2 13:28
sqlite中文怎么转码啊,有方案或者教程吗?

我都是utf-8,因为是Linux的平台,vba你搜一下,有相关的驱动,应该不涉及中文转码的。

TA的精华主题

TA的得分主题

 楼主| 发表于 2021-3-2 15:39 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
opiona 发表于 2021-3-2 12:24
不知道是否能提高效率

非常非常非常的感谢提供的思路,我在你的基础上把[查询a]这个表也放进去做union all了,你目前的代码会漏掉我[查询a]这个表的数据,因为[查询a]是我的主查询。

  1. sql = ""
  2.     sql = sql & "select 客户编码"
  3.     sql = sql & ",MAX(客户名称) AS 客户名称"
  4.     sql = sql & ",SUM([余额]) AS [余额]"
  5.     sql = sql & ",SUM([任务1月]) AS [任务1月]"
  6.     sql = sql & ",SUM([任务2月]) AS [任务2月]"
  7.     sql = sql & ",SUM([销量1月])  AS [销量1月]"
  8.     sql = sql & ",SUM([销量2月]) AS [销量2月]"
  9.     sql = sql & " from ("
  10.     sql = sql & "select 客户编码,NULL AS 客户名称"
  11.     sql = sql & ",IIF(月份=1,任务,0) AS [任务1月]"
  12.     sql = sql & ",IIF(月份=2,任务,0) AS [任务2月]"
  13.     sql = sql & ",0 AS [销量1月]"
  14.     sql = sql & ",0 AS [销量2月]"
  15.     sql = sql & ",0 AS [余额]"
  16.     sql = sql & " from [任务d$]"
  17.     sql = sql & " UNION ALL "
  18.     sql = sql & "select 客户编码,NULL AS 客户名称"
  19.     sql = sql & ",0 AS [任务1月]"
  20.     sql = sql & ",0 AS [任务2月]"
  21.     sql = sql & ",IIF(月份=1,销量,0) AS [销量1月]"
  22.     sql = sql & ",IIF(月份=2,销量,0) AS [销量2月]"
  23.     sql = sql & ",0 AS [余额]"
  24.     sql = sql & " from [销量e$]"
  25.     sql = sql & " UNION ALL "
  26.     sql = sql & "select 客户编码,NULL AS 客户名称"
  27.     sql = sql & ",0 AS [任务1月]"
  28.     sql = sql & ",0 AS [任务2月]"
  29.     sql = sql & ",0 AS [销量1月]"
  30.     sql = sql & ",0 AS [销量2月]"
  31.     sql = sql & ",[余额]"
  32.     sql = sql & " from [余额c$]"
  33.     sql = sql & " UNION ALL "
  34.     sql = sql & "select 客户编码,客户名称"
  35.     sql = sql & ",0 AS [任务1月]"
  36.     sql = sql & ",0 AS [任务2月]"
  37.     sql = sql & ",0 AS [销量1月]"
  38.     sql = sql & ",0 AS [销量2月]"
  39.     sql = sql & ",0 AS [余额]"
  40.     sql = sql & " from [名称b$]"
  41.     sql = sql & " UNION ALL "
  42.     sql = sql & "select 客户编码"
  43.     sql = sql & ",0 AS [客户名称]"
  44.     sql = sql & ",0 AS [任务1月]"
  45.     sql = sql & ",0 AS [任务2月]"
  46.     sql = sql & ",0 AS [销量1月]"
  47.     sql = sql & ",0 AS [销量2月]"
  48.     sql = sql & ",0 AS [余额]"
  49.     sql = sql & " FROM [查询a$a2:a5000]"
  50.     sql = sql & ") "
  51.     sql = sql & " GROUP BY 客户编码 "
复制代码

TA的精华主题

TA的得分主题

发表于 2021-3-2 15:55 | 显示全部楼层
语虚何以言知 发表于 2021-3-2 13:28
sqlite中文怎么转码啊,有方案或者教程吗?

如果要做大还是用mysql吧,sql语法大同小异,再进阶点用orm,换数据库只要换引擎就可以了,查询代码都不用改。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 06:21 , Processed in 0.057302 second(s), 14 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表