mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和


大家好!《全民一起用SQL》课程上线以来,很多同学都提出过这样的问题:“能不能在Excel里用SQL命令?”、“SQL查询很强大,但难道要把Excel数据导到数据库里去查询吗?”,以及“单位电脑上没有数据库,我怎么练习SQL呢” ……

其实上述问题都可以轻松解决 —— 因为Excel本身就能用SQL

不仅能用,而且微软这一次支持的还是标准SQL语法(每次想到这里就要忍不住diss一下Word的正则表达式,偏要用微软自己的WildCard语法)。

这就意味着,在Excel中需要多次复杂操作才能解决的查询问题,我们只要写几行SQL命令就能轻松搞定,并且可以保存代码,将来反复重用。


举一个具体的案例。假设某集团用三个工作表保存重要信息:

1

部门表,记录各个部门的编号和名称等:

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_02

2

员工表,记录全体员工的信息,包括工号、姓名,以及所在部门的编号:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_03

3

业务表,记录每一笔筹款业务流水,即 “在XX时间,工号为XX的员工筹到了XX元”:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_04



现在老板发话:请统计出每个部门全体员工的筹款总额,如下表所示:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_05



大家不妨先想一想:我们平时处理这个需求时,需要多少时间?如果每天统计一次,又该怎么办?

当然,VBA、Python乃至PowerQuery都可以解决这个问题。不过从本人的感受看,最简单粗暴的办法,就是使用SQL命令。短短四行语句就能搞定,而且可以保存起来随时重复使用

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_06

只不过,你需要学过一点SQL,并且愿意忍受这个让人忆苦思甜的上古界面:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_07

有些朋友大概已经注意到了这个工具的名字 —— 就是上图左上角的“Microsoft Query”,简称 “MS Query” ,熟悉Access的朋友对它一定更不陌生。这是微软在Excel 2007版开始引入的一个工具,用途就是从SQLServer等外部数据库中,使用SQL语句查询数据、然后导入到Excel里。

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_08

听起来好像还是停留在 “用SQL语句查询数据库里的数据” 的层面上,那我们怎么可能用它来查询Excel呢?

原因很简单:在MS Query眼中,Excel工作簿也算一个数据库。

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_09

这样问题就解决了:我们只要在Excel中启动MS Query,然后让它去连接保存原始数据的那个Excel文件,接下来就可以在MS Query里写SQL语句、从那个Excel文件中查询出结果,最后保存到Excel中。

下面就以这个统计部门业绩的需求为例,逐步介绍一下这几个环节:

1. 启动MS Query

在安装Excel时,MS Query就已经默认安装好,保存在 “数据” 选项卡内。以杨老师使用的Office 365为例,可以通过下面的菜单找到这个功能(其他版本Excel大同小异):



mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_10

点击子菜单后,就会看到一个风格质(jian)朴(lou)的界面,这就是MS Query启动后的第一个窗口,让我们指定原始数据所在的数据库

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_11

2. 连接原始数据工作簿

既然要对Excel文件做查询,自然就要在这个窗口中选择“Excel Files”。不过再进行下一步之前,请一定把窗口下面 “使用查询向导” 这个复选框取消掉,否则在进入SQL查询界面之前要先完成很多不必要的操作。

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_12

然后点击“确定”,就会切换到MS Query的主界面以及文件选择对话框。在里面选择具体要查询的Excel工作簿,然后点击“确定”

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_13

接下来,MS Query就会读取这个工作簿中的内容,把其中包含的工作表显示在对话框中让我们选取:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_14

显然在这个案例中,若想得到最终报告,我们需要从业务表中统计每个员工的筹款总额、再从员工表中取得每个员工所在部门编号,最后再到部门表中取得这些编号对应的部门名称。所以必须把员工表、部门表、业务表都导入到MS Query中才行。

不过 MS Query 每次只允许导入一张表,所以大家要执行三次 “选中表名 —> 点击添加按钮” 的操作,而不能像后来者Power Query那样,直接复选多张表格。

添加完成后,如果看到类似下面的界面,就说明我们的数据表已经导入成功、随时可以用SQL对它们做查询了:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_15

这里需要注意:MS Query默认将Excel工作表的第一行认为是字段名称。所以如果工作表的第一行是空白行,那么导入时会被认为没有字段名,因此建议大家事先删除最前面的空白行

3. 编写和执行SQL命令

在上面这个界面中,只要点击工具栏上的 “SQL” 图标,就可以弹出一个极简主义的窗口,仅有的五个文字 “SQL语句” 告诉我们,这就是写SQL命令“编辑器” 。

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_16

试写一个简单的SQL语句,可以看到它既没有智能提示、也没有语法高亮,粗糙的字体上每一个像素都在控诉着开发者的冷峻:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_17

界面虽然惨了点,但是功能确是没的说 —— 点击右边的 “确定” 按钮,一瞬间就在主界面中看到了预期结果,也就是所有45岁以下的员工。

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_18

到这里,我们就可以自由发挥《全民一起玩SQL》学过的SQL语法,像对待MySQL或SQLServer一样实现各种查找。不过必须提醒读者的是:毕竟Excel不是真正规范的关系型数据库,所以单独规定了一些特殊的用法和要求。其中主要的几项是:

  1. 表名后面要加上半角美元符号 $ 。比如本例中,Excel显示的工作表名字是“员工表”,而在数据库查询时就要写成 “员工表$” ;
  2. 在SQL语句中,最好给表名套上反引号(键盘Esc键或波浪线键上方)或方括号;
  3. 在杨老师使用的Excel中,似乎不支持 INNER JOIN 关键字,要使用《全民一起玩SQL 基础篇》中讲过的逗号简写形式才可以。比如标准SQL中的 “SELECT * FROM a INNER JOIN b ON a.id=b.id” ,在这里就要写成  “SELECT * FROM a, b WHERE a.id=b.id” 。(我记得以前好像在MS Query里用过INNER JOIN,不知是不是Excel版本问题)
  4. 在进行多表连接时,建议给参与连接的每个表都分别起一个别名,否则容易出错;
  5. 如果执行时出现错误提示 “参数不足” ,基本上就是SELECT、WHERE 等子句中,某个字段的名字写错了:
  6. 如果已经使用某个SQL代码查询出若干结果,那么再次点击工具栏SQL按钮,MS Query会自动把你写的代码转换为“规范形式”,也就是所有列名前都补全所在表名、所有表名前都补全所在工作簿的文件名,看起来比较繁冗。如果大家不喜欢这种写法,一定要在执行SQL语句之前、先把自己的代码复制一份

4. 完成查询任务

万事俱备,现在就可以回到部门业务统计这个案例了:



mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_19

我们的任务是统计出每个部门全体员工的筹措资金总和,根据学过的SQL知识,最直接(但效率较低)的思路如下:

  1. 业务表员工表连接在一起,就可以得到一个新的临时表。这个临时表的每一条业务数据里,都会显示出该业务属于哪个部门的。连接线索是:业务表.员工工号 —> 员工表.工号 —> 员工表.部门编号 ;
  2. 再将上面的临时表与部门表连接在一起,得到一个更大的临时表。这个表格中的每一条业务记录中,又会多出一个“部门名称”,即连接线索是:业务表.员工工号—>员工表.工号—>员工表.部门编号—>部门表.部门名称;
  3. 在这个大表中,按部门编号分组,并用 SUM 函数统计每个部门的业务总和即可:
  4. 考虑到最终SELECT子句中还要显示部门名称,所以根据SQL标准语法,SELECT 中的字段必须都出现在 Group By 里,于是再把部门名称也放到 Group By 中。

上述过程一步步写好,就是下面这个SQL命令(为了便于阅读,下面用VS Code编辑器展示):

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_20

拷贝到 MS Query编辑器中运行,很快就会看到最终结果:

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_21

短短几行SQL命令,轻松完成跨表查询!如果同学有兴趣,还可以思考一下能否对这个查询进行优化,比如先做分组统计,从而减少临时表的长度等等。

5. 保存结果到Excel

在MS Query中得到结果后,只要点击工具栏的保存按钮就可以保存本次工作成果。具体来说,工具栏上有两个保存按钮,分别用于保存这次编写的SQL语句、以及这次得到的查询结果



mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_22

如果选择保存查询结果,就会弹出一个对话框,并让我们选择“保存到新工作表”还是“当前工作表某个单元格”,接下来我想大家就知道怎么做了。

mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_23



mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 查询总和_24

以上就是在Excel中使用SQL语句的完整过程。具体还有很多细节,大家可以自己感受并总结,有心得也欢迎各位随时来Q群讨论。我们随后会将这篇公众号作为“精选文章”发布到官网 www.ukoedu.com 的《全民一起用SQL》课程中,并将所用Excel案例文件和查询代码作为附件上传。

最后,我想一定还有很多同学对这个SQL编辑器的丑陋耿耿于怀。为什么不能用些心思,把这个功能做细致一点呢?

我猜大概是因为,盖茨先生根本没想到,我们这些用户居然真的懂SQL、真的会用到这个功能吧 ……

mysql sql结果作为一个表名查询 sql查询结果作为新表_mysql sql结果作为一个表名查询_25



mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_26

END



mysql sql结果作为一个表名查询 sql查询结果作为新表_sql 临时表_27