在EXCEL中使用SQL语句查询集锦-持续更新中，敬请关注

opiona · 发表于 2012-4-24 00:02

本帖最后由 opiona 于 2012-6-22 23:40 编辑

SQL语句在数据库使用中十分重要。
在EXCEL中可以不打开工作簿，就获取数据，对多工作簿操作很用，也很快。
对大量数据处理，比循环快很多，但是比词典方法还有点距离（可惜我还没有学会词典）。
对数据库编程，任何编程语言都要，都支持SQL语言。

借助VBA, 我们也可在EXCEL中使用它，也就是将EXCEL作为数据库
（EXCEL中对SQL功能稍有限制，如：删除数据等，但不影响我对它的推崇）
在斑竹的帮助下，对SQL在EXCEL有了一点点体会，拿出来和大家分享。
斑竹的SQL帮助文件：http://club.excelhome.net/thread-65664-1-1.html
标准的EXCEL中SQL使用见附件的各个模块，ALT+F11就能看到了{:soso_e120:}
2012-04-24 最新更新：标准语句、实例语句和解释互动，结果直接显示，可自己修改语句。
EXCEL2003版本：

EXCEL中的SQL学习总结-for 2003-20120424.rar (30.28 KB, 下载次数: 6550)
EXCEL2007版本：

EXCEL中的SQL学习总结-for 2007-20120424.rar (41.11 KB, 下载次数: 11851)

希望大家踊跃回复，提出SQL的问题，
共同研究，共同学习，共同进步！！

'在Excel中使用SQL语句总结-1:
'************************************ 标准SQL查找代码： *********************************************************
Sub SQL_Excel_2003_2007()
'On Error Resume Next '如果出现错误，忽略，然后执行下一行代码。
Application.ScreenUpdating = False '关闭屏幕刷新,成对出现，提高速度
Application.DisplayAlerts = False '关闭提示，,成对出现，避免出现提示框
'--------------------------------------- 参数声明部分 ------------------- ------------------- -------------------
Dim cnn, SQL$ '定义数据库连接和SQL语句
Set cnn = CreateObject("adodb.connection") '创建数据库连接
Set rs = CreateObject("adodb.recordset") '创建一个数据集保存数据
'--------------------------------------- 设置数据库连接 ------------------- ------------------- -------------------
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
'将EXCEL文件作为数据库连接，实际并不打开EXCEL，
'Excel2003版本：cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\数据表.xls"
'Excel2007版本：cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据表.xlsx"
'带参数的连接字符串：cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;HDR=yes;IMEX=2';data source=" & ThisWorkbook.FullName
' HDR=Yes 代表 Excel 档中的工作表第一行是标题栏，标题只能是一行，不能使多行，或者合并的单元格。
' HDR=no 工作表第一行就是数据了，沒有标题栏，不使用栏位，则栏位就以f代表，第一列列名就是：f1,第二列列名：f2
' IMEX 汇入模式 0 只读 1 只写 2 可读写
' 当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
' 当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
' 当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
'Data Source 存储查询数据来源的工作薄名称，数据库路径为：数据表.xls 或本表：& ThisWorkbook.FullName
'--------------------------------------- 设置SQL语句 ------------------- ------------------- -------------------
SQL = "select * from [数据表_1$A1:G100] where 姓名='马拉多纳'"
'在Sheet1表内查找列名为：姓名中所有：马拉多纳的数据。
'[XXX$A2:G100]的中括号和$为特别数据表标示符，XXX为Sheet名，A2:G100是选取的区域。
'SQL语句是一个字符串，双引号开头和结尾，列名两边无单引号，表示一个字符串：‘马拉多纳’，要用单引号扩上，数字就不用了。
'Nu=36: AA="马拉多纳": set Sh=Sheet1: SQL = "select * from [" & Sh.name & “$] where 姓名=‘” & AA & “’ and 年龄=Nu"
'--------------------------------------- SQL结果处理 ------------------- ------------------- -------------------
Set rs = cnn.Execute(SQL) '将SQL语句获得的数据传递给数据集
Sheets("结果").Cells.ClearContents '清理保存数据的区域
Sheets("结果").Range("a2").CopyFromRecordset rs '将数据集粘贴到Excel中，左上角为A2，无列名。
'Sheets("结果").Range("b2").CopyFromRecordset cnn.Execute(SQL) '可以不声明Y，直接使用
cnn.Close '关闭数据库连接
Set cnn = Nothing '将CNN从内存中删除。
'--------------------------------------- ------------------------ ------------------- -------------------
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub '这就是最简单的EXCEL中SQL的应用，其他的都是SQL的运用了

复制代码

该贴已经同步到 opiona的微博

补充内容 (2018-7-17 09:03):

明细账求：小计合计总计排序  请前往322楼

补充内容 (2018-11-24 10:20):
转置后，进行反转置的函数：见330楼

补充内容 (2018-12-23 19:18):

十万，百万数据保存到SQL Sever数据库，耗时几秒--几十秒
详见：337楼

补充内容 (2020-1-8 12:13):
年月日分级汇总见391楼

补充内容 (2021-12-20 17:04):
DLL文件已经更新  见楼层: 432

补充内容 (2022-3-27 09:50):
见435楼: ACCESS保存和读取图片(文件)

补充内容 (2022-6-5 10:01):
DLL文件已经更新  见楼层: 437

补充内容 (2022-8-1 13:25):
DLL文件已经更新  见楼层: 438

补充内容 (2022-8-1 13:26):
加入 MySqlBulkLoader功能 DLL文件已经更新  见楼层: 439

补充内容 (2023-8-10 20:17):
十万，百万数据保存到SQL Sever数据库，耗时几秒--几十秒
单开了帖子: https://club.excelhome.net/thread-1666858-1-1.html

opiona · 发表于 2012-4-24 00:03

本帖最后由 opiona 于 2013-2-4 18:08 编辑

下面是收集的一些链接：
SQL语句中常用的一些函数，见5楼：

SQL将几个条件组合一句中，居然用到3个别名：
http://club.excelhome.net/forum.php?mod=viewthread&tid=859194&page=5#pid6056804

Imex参数，SQL查询数字和文本混排的数据：
http://club.excelhome.net/forum.php?mod=viewthread&tid=859194&page=5#pid6191850

获取全部字段名：
http://club.excelhome.net/forum.php?mod=viewthread&tid=859194&page=6#pid6243359

EXCEL中调用SQL语句的自定义函数，见71楼：
http://club.excelhome.net/thread-859194-8-1.html

补充内容 (2013-6-12 13:50):
将SQL的查询结果按照指定顺序排序的方法：
http://club.excelhome.net/forum. ... ;page=13#pid6997426

补充内容 (2014-1-17 13:50):
EXCEL中调用SQL语句的自定义函数不在71楼，而是70楼
http://club.excelhome.net/thread-859194-7-1.html

opiona · 发表于 2012-4-24 00:04

本帖最后由 opiona 于 2013-2-4 17:51 编辑

----------------------------------------------------------------------------------------------------------------------
1：如果sheet1 和 Sheet2 分别在2个工作簿，SQL应该怎么写？
答案是：

Cn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
Dataname = ThisWorkbook.Path & "\数据表.xls"
'Strsql = "SELECT a.姓名, b.工资 from [Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b] as b, [Sheet1$a:b] as a where a.姓名=b.姓名 order by a.姓名"
Strsql = "SELECT 姓名, 工资 from [Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b] where 姓名 in (select 姓名 from [Sheet1$a:b] )"
'答案找到了：[Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b]

复制代码

2：有没有办法SQL直接给出按照sheet1中A列顺序排序的结果
答案是：

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据表.xlsx"
SQL = "select b.工资, b.补贴 from [Excel 12.0;Database=" & ThisWorkbook.FullName & "].[Sheet1$] a left join [Sheet1$] b on a.姓名=b.姓名"
' ThisWorkbook.Path & "\数据表.xls--------b表是数据表，
' ThisWorkbook.FullName--------a表是条件表，
' 按照a表顺序，在b表查找，结果按照a表顺序粘贴（原顺序不表，结果对应粘贴）
' 注意：b表的数据不能重复的。

复制代码

请关注版主的回复：http://club.excelhome.net/thread-789520-1-1.html
======================================================================================

在表A 中找表B 中没有的数据
去掉：WHERE NOT EXISTS 中的NOT，也许就是2个表都有的数据
判断字段可以不仅仅是一个，可以是多个：WHERE A." & ZD字段名称A & "=B." & ZD字段名称B & " and  A." & ZD字段名称2A & "=B." & ZD字段名称2B & " )"
[code=vb]
Sub 大小表() '大表 as A 中找小表 as B 中没有的数据
'On Error Resume Next
Application.ScreenUpdating = False '关闭屏幕刷新
Application.DisplayAlerts = False '关闭提示
'----------设置参数-，假设2个表在一个工作簿中，在2个工作簿，请参照楼上------------------------------------------------------------------------------
Set sh = Sheets("多的数据")  'A表
Set sh1 = Sheets("少的数据")  'B表
ZD字段名称A="姓名" 'A表中判断不同的字段名称，多数情况2个表字段是一样的。
ZD字段名称B="人名" 'B表中判断不同的字段名称
Set sh3 = Sheets("结果")
sh3.Cells.ClearContents
'----------数据库连接-------------------------------------------------------------------------------
Dim StrSQL$, Cn As Object
Set y = CreateObject("adodb.recordset")
Set Cn = CreateObject("Adodb.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;imex=1';Data Source=" & ThisWorkbook.FullName
  StrSQL = ""
  StrSQL = StrSQL + " SELECT * FROM [" & sh.Name & "$] as A "
  StrSQL = StrSQL + " WHERE NOT EXISTS ("
  StrSQL = StrSQL + " SELECT * FROM [" & sh1.Name & "$] as B "
  StrSQL = StrSQL + " WHERE A." & ZD字段名称A & "=B." & ZD字段名称B & ")"
  Set y = Cn.Execute(StrSQL)
  sh3.Range("a2").CopyFromRecordset y
'----------获取表头-------------------------------------------------------------------------------
For L = 0 To y.Fields.Count - 1
      sh3.Cells(1, L + 1) = y.Fields(L).Name
Next L
'----------后续处理-------------------------------------------------------------------------------
Cn.Close
sh3.Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
[/code]

比较长的SQL语句，建议分段书写，这样读写和理解都方便。
  StrSQL = ""
  StrSQL = StrSQL + " SELECT * FROM [" & sh.Name & "$] as A "
  StrSQL = StrSQL + " WHERE NOT EXISTS ("
  StrSQL = StrSQL + " SELECT * FROM [" & sh1.Name & "$] as B "
  StrSQL = StrSQL + " WHERE A." & ZD字段名称A & "=B." & ZD字段名称B & ")"

opiona · 发表于 2012-4-24 00:05

本帖最后由 opiona 于 2012-5-27 11:07 编辑

没有列标题，或列标题重复、合并等情况，要用到HDR=NO
用变量组织一个很长的SQL查询语句例子见下帖 9楼代码：
http://club.excelhome.net/forum.php?mod=viewthread&tid=872937&page=1#pid5972465

SQL语句中注意：的文本变量（和文本常量）要用单引号，数字类型的不能加单引号：
Str = Str & " '" & Str经销商 & "'" & " as 经销商,"
where 名称<>'合计' and abs(数量)>0"

opiona · 发表于 2012-4-24 00:06

本帖最后由 opiona 于 2013-2-4 18:11 编辑

详见附件： SQL中常用的函数.rar (14.76 KB, 下载次数: 1951)

2013-2-4 18:06 上传
点击文件名下载附件

'在SQL查询中常用的函数:
' SUM(字段名) 把数据栏的值相加
StrSQL = "SELECT 班级,SUM(分数) AS 合计 FROM [" & SH1.Name & "$] GROUP BY 班级"
' COUNT(字段名) 对数据行数的统计或对某一栏有值的数据行数统计
StrSQL = "SELECT 班级, COUNT(姓名) AS 人数 FROM [" & SH1.Name & "$] GROUP BY 班级"
' MAX(字段名) 取得一个表格栏最大的值，文本时最大ASII值
StrSQL = "SELECT 班级, MAX(分数) AS 分数 FROM [" & SH1.Name & "$] GROUP BY 班级"
' MIN(字段名) 取得一个表格栏最小的值，文本时最小ASII值
StrSQL = "SELECT 班级, MIN(分数) AS 分数 FROM [" & SH1.Name & "$] GROUP BY 班级"
' IIF(字段名>0,字段名,NULL) 条件函数
StrSQL = "SELECT 班级, SUM(IIF(性别='男',分数,0)) AS 男分数合计,SUM(IIF(性别='女',分数,0)) AS 女分数合计 FROM [" & SH1.Name & "$] GROUP BY 班级"
' AVG(字段名) 得出一个表格栏平均值
StrSQL = "SELECT 班级,ROUND(AVG(分数),2) AS 平均分 FROM [" & SH1.Name & "$] GROUP BY 班级"
' LAST和FIRST(字段名) 分组汇总时，提取其他（非汇总）字段部分
StrSQL = "SELECT 班级,SUM(分数) AS 合计,FIRST(姓名) AS 代表 FROM [" & SH1.Name & "$] GROUP BY 班级"
' HAVING函数，获得函数表达的指定条件:
' SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件)
StrSQL = "SELECT 班级, SUM(分数) AS 合计 FROM [" & SH1.Name & "$] GROUP BY 班级 HAVING SUM(分数) > 250"
'
'VBA中部分函数也可用于SQL的查找部分和条件部分
' 如：FORMAT(日期,'YYYY-MM-DD')、INSTR(姓名,'王')>0、ROUND(AVG(分数),2) 等等。
StrSQL = "SELECT * FROM [" & SH1.Name & "$] WHERE INSTR(姓名,'王')>0"

复制代码

AVEL · 发表于 2012-4-24 00:22

占位，学习。
{:soso__4011813854091704040_4:}

lhsh36183 · 发表于 2012-4-24 16:11

本帖最后由 lhsh36183 于 2012-4-24 16:12 编辑

占位，学习.请楼主做个2003版本。

opiona · 发表于 2012-4-24 16:33

本帖最后由 opiona 于 2012-4-24 16:39 编辑

lhsh36183 发表于 2012-4-24 16:11
占位，学习.请楼主做个2003版本。

'Excel2003版本：cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" &
'Excel2007版本：cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" &

就是讲代码中的上两句互换一下即可！

见1楼

直达车 · 发表于 2012-4-25 20:30

期待中{:soso_e183:}

tsh134 · 发表于 2012-4-26 08:33

道个谢，期待大作

		自动登录	找回密码
密码			免费注册

[分享] 在EXCEL中使用SQL语句查询集锦-持续更新中，敬请关注

评分

评分

评分