米乐app官方-米乐m6苹果官网下载 > 化工知识 > sql入门教程第04课：sql查询中"表"技巧的总结

sql入门教程第04课：sql查询中"表"技巧的总结-米乐app官方

时间：2021-12-21 来源：浏览：

sql入门教程第04课：sql查询中"表"技巧的总结

原创 eh看见星光 excel星球

excel星球

微信号 ahaexcel

功能介绍建议常用excel的职场人关注，海量教程随学随用，随用随查。主创：看见星光，微软全球最有价值专家、excel图书作者、培训师。内容：每日四文，一篇函数教程、一篇vba教程、一个短视频小技巧、一篇杂文。

收录于话题

每天一篇excel技术图文

微信公众号：excel星球

no.568-表里表气

作者：看见星光

微博：excelers / 知识星球：excel

hi，大家好，我是星光。上期我们聊了sql常用查询语句中的字段查询，其简化版语法如下：

select 字段名 from 表名

当场我就……小声说了，关键字f rom指明了要获取字段信息的表名。倘若数据源是excel表格，则需要在表名后增加美元符号$，并用中括号包起来，例如[sheet1$]……

事实上，上述例子是sql in excel 对工作表引用最简单的一种情况，也就是整表引用；此外还有单元格区域引用、跨工作簿引用等。

所以咱们今天就再来集中聊一下sql语句中的excel表。

1.区域成表

excel工作表和数据库的数据表有很多不同之处，最显著的地方在于，数据库的数据表可以理解为由行列构成，而excel工作表则是由一个又一个单元格构成，且这些单元格拥有独特的地址表述方法，也就是a1或r1c1，它们还可以构成数据相连的单元格区域，例如a2:h8。

那么问题来了，如果我们只需要计算某张excel工作表的部分区域的话，sql该怎么表述呢？

这种问题是很常见的。

比如，很多人的excel标题行并不是处于表格的第一行，而是第2行……

如下图所示▼

此时，我们希望计算a2:f列的单元格区域，这样我们更容易使用字段名处理数据，而不是整张excel工作表……

再比如，一张表里存在两个或更多个“表”……这句话什么意思呢？见下图▼

图中所示的表格中，既存在一份“教师表”，又存在一份“学生表”；如果我们只希望sql引用计算a2:d8的教师表数据……

呃，年轻人火大容易肾虚呐……

……excel中的sql其实是支持将工作表的单元格区域作为“表”使用的。

上图所示的问题，sql可以写成：

select 姓名,学科 from [数据表 $a2 :d8]

查询结果如下：

而第1种情况，我们知道数据开始于a2单元格，但不知道结束于f列的哪个单元格，sql可以写成：

select 姓名,爱好 from [学生表 $a2 :f]

另外，如果我们需要sql引用计算表格d:g整列的数据，sql可以写成：

select * from [学生表 $d :g]

总结以上几种excel工作表区域的表述方式，也就是，工作表名称美金符号$ 相对引用状态下的单元格地址，最后使用中括号包起来。

就酱紫。

本节小贴士：

[学生表$a2:f]，我们说该语句可以引用从a2至f列最后存在数据的单元格区域，但这是有一个限制前提条件的，即非自连接状态。所谓自连接是指sql应用于链接自身的工作簿。自链接状态下，a2:f的表达方式最多是a2:f65536行；倘若此时需要的引用行超过65536行，请使用整表模式。

2.跨工作簿的表

一个众所周知的问题是，excel函数在处理跨工作薄数据时很是疲态，除了个别几个查找引用类函数（例如vlookup等），绝大部分函数都需要打开相关工作簿后才可以计算使用。

是的，vlookup函数并不需要打开相关工作簿也可以跨工作薄使用，而且在vlookup公式书写完成后，即便你把它所引用的工作簿给删了，也不妨碍它计算，这是因为它已经把相关数据缓存到了公式所在的工作簿中，不过vlookup这种模式并不支持函数复杂嵌套……打个响指，关于这一点，如果你感兴趣，我们改天单独聊一下。

……咳，说回sql~~

……我们之前分享的sql语句都是处理当前工作簿的表格，如果我们所需要处理的数据位于其它工作簿时，sql该怎么表述呢？

例如，获取位于计算机d盘的“eh小学”文件夹下的“学生表.xlsx”工作簿中的“成绩表”的所有数据——一口气读完这话的，不得不让在下心生佩服。

如果是ole db法（该方法参考本系列教程第1章），sql语句如下▼

select * from [d:eh小学学生表.xlsx] . [成绩表$]

from后指定表字符串有两个部分构成，第一个中括号内是指定工作簿的存放路径带后缀的完整工作簿名称，后一个中括号内是工作表名称，两个中括号之间使用英文点号（.）相连。

如果是通过vba ado使用sql语句……

敲书柜前方预警：vba基础差的童鞋请自行跳过以下内容……

相比于ole db法，vba ado的方法要灵活的多，它可以使用ado直接创建并打开与指定工作簿的链接，因此sql语句就无需再指定工作簿完整名称等。

代码参考如下▼

sub ado_sql() ’适用于除2003版以外的高版本excel dim cnn as object, rst as object dim strpath as string, strcnn as string, strsql as string dim i as long set cnn = createobject( "adodb.connection" ) strpath = "d:eh小学学生表.xlsx" ’指定工作簿 strcnn = "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & strpath cnn.open strcnn ’ 创建并打开到指定工作簿的链接 strsql = "select * from [成绩表$]" ’strsql语句，查询成绩表的所有数据 set rst = cnn.execute(strsql) ’ 执行strsql cells.clearcontents for i = 0 to rst.fields.count - 1 cells( 1 , i 1 ) = rst.fields(i).name next range ( "a2" ).copyfromrecordset rst cnn.close set cnn = nothing end su

以上代码第7行直接指定了需要连接的工作簿完整名称，sql语句内也就不再需要特别处理。但更多的情况是，ado创建的链接是一个工作簿，需要获取的数据在另一个或多个工作簿，例如两个工作簿之间的数据查询统计。此时通常使用的代码如下▼

sub ado_sql2() ’适用于除2003版以外的高版本excel dim cnn as object, rst as object dim strpath as string, strcnn as string, strsql as string dim i as long set cnn = createobject("adodb.connection") strpath = thisworkbook.fullname ’代码所在工作簿的完整名称 strcnn = "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & strpath cnn.open strcnn ’创建到代码所在工作簿的链接 strsql = "select * from [excel 12.0;database=d:eh小学学生表.xlsm].[成绩表$]" set rst = cnn.execute(strsql) ’执行sql cells.clearcontents for i = 0 to rst.fields.count - 1 cells(1, i 1) = rst.fields(i).name next range("a2").copyfromrecordset rst cnn.close set cnn = nothing end sub

代码中第7行创建了当前工作簿的链接，sql语句中又指定了另外一个工作簿的链接。sql语句如下▼

select * from [excel 12.0 ;database=d:eh小学学生表.xlsx].[成绩表$]

from指定表的字符串有两部分组成。第一个中括号中，excel 12.0是目标工作簿的版本号，第2章时我们讲过，excel 12.0适用于除了2003以外的所有excel版本。database指定的是数据源工作簿的路径和名称。第2个中括号内是工作表名。两个中括号之间使用英文点号相连。

……

看起来似乎vba ado方法的sql语句比ole db法更复杂？ 确实如此，不过前者的功能也更强大。比如，它可以通过vba对象的属性、方法，循环和判断语句等，有条件的筛选工作簿和工作表……相比之下，ole db中的sql语句就是纯手工常量模式了。当然，更重要的是，前者不但可以查数据，还可以增改删数据，后者却只限于查。

关注我，下期再贱。示例文件下载，百度网盘..▼ https://pan.baidu.com/s/1tvhyylg2cpdd5ebdon_vkg 提取码: et58

需要系统学习excel，却找不到优质教程？学习excel的过程中遇到疑难问题，却找不到人及时作出解答？加入我的付费社群，和微软最有价值专家（mvp）全面精进excel，学习答疑都不再是问题……

加入我的excel会员，全面学习excel

透视表函数图表 vba pq想学啥学啥

本文由公众号“excel星球”首发。

点击 阅读原文 ，加入excel会员社群！

上一条：什么是直方图？

下一条：origin线性拟合95%置信带、预测带

米乐m6苹果官网下载的版权：如无特殊注明，文章转载自网络，侵权请联系cnmhg168#163.com删除！文件均为网友上传，仅供研究和学习使用，务必24小时内删除。