SQL在Excel中的应用方法

iamlaosng文Excel中使用SQL的主要目的是连接（或Excel工作表）导入数据或者对这些数据进行统计汇总，要达到这个目的，需要好好学习SQL语句的使用。

本文主要说明在Excel中如何使用SQL，至于SQL语句本身就不多作介绍了。

一、简单的查询1、建立查询数据选项卡—现有连接—浏览更多或者按快捷键Alt+D+D+D选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。

表现形式可以是表，也可以是数据透视表等。

2、SQL查询语句如果是挑选部分列数据，就需要用SQL语句（取所有数据也可以用SQL语句）。

建立查询时，选择工作表后不要点击“确定”按钮，而是先点击“属性”按钮，弹出窗口中选择“定义”选项卡，在命令文本框中输入SQL查询语句（原来的工作表名称，表示所有数据，可以认为是取所有数据的SQL的一种特殊写法）：Select 字段列表 from [工作表名$]--其中字段列表就是需要选择的字段，数据源用工作表名称加“$“再用中括号括起来，例如：select prov_name, city_name, xs_mc, xs_code from [Sheet1$]select * from [Sheet1$]-- 取所有数据偶然发现，字段名不能用no，估计是保留字，如需要，用中括号括起来，例如：select [no],prov_name,city_name, xs_mc, xs_code from [Sheet1$]字段名中含有特殊字符的也要用中括号括起来，如/ 空格等Excel查询没有伪表概念，对于表达式的计算直接用select既可，例如Select 23+45 -- 返回68Select date() -- 返回当前日期3、修改查询语句方法：点击右键—弹出菜单—表格—编辑查询通过修改SQL语句可以变更所取的数据，也可以将建立查询时的简单SQL语句改成复杂的SQL语句。

字段名更换：如果想换个字段名，用“as 新字段名”既可，例如：select prov_name as 省, city_name as 城市, xs_mc as 县市, xs_code as 编码 from [Sheet1$]非正常表格：数据区域（含字段名）不在第一行需要在工作表名称后面指定数据范围，例如：select prov_name, city_name, xs_mc, xs_code from [Sheet1$B2:G2000]或者，将数据块定义为一个名称，假设定义为mydata，SQL语句如下：select prov_name, city_name, xs_mc, xs_code from mydata注意：使用名称时没有$符号，也没有方括号了。

数据更新：数据源发生变化，需要更新数据，方法：点击右键—弹出菜单—刷新意外：如果打开Excel文件后弹出不是选择工作表的窗口而是一个“数据连接属性”窗口，可以关闭这个窗口，然后将Excel应用极小化再极大化方式消除，或者在弹出选择文件的窗口时，退回上一级文件夹，删除那个Queries文件夹，就行了。

4、外部数据属性修改SQL语句后，如显示格式不是预想的那样，需要去掉“外部数据属性”中“保留列属性”前面的勾选。

方法：点击右键—弹出菜单—表格—外部数据属性，弹出窗口如下：二、复杂的查询1、多表联合相同结构的多个表合并到一起，用union连接SQL语句，例如：Select * from [财务部$] union all Select * from [市场部$]Union是去重复的，即相同的记录保留一个（类似distinct），Union all则是直接相加两个结果，不去重复。

增加一个部门字段可以将查询结果中的区分开来，以便知道数据来自哪个表。

Union 的三个一致，即：字段的数量、类型和顺序。

例如：Select “财务部” as 部门,* from [财务部$] union all Select “市场部” as 部门,* from [市场部$]多表联合查询Select * from [部门$] bm,[员工$] yg where bm.部门编码=yg.部门编码跨工作簿查询如果数据不仅来自不同的工作表，还来自不同的文件，一样可以用union联合，例如：Select “分公司1” as 公司, “财务部” as 部门, * from [F:\SQL之Excel应用\分公司].[财务部$] union allSelect “分公司1” as 公司, “市场部” as 部门, * from [F:\SQL之Excel应用\分公司].[市场部$] union allSelect “分公司2” as 公司, “财务部” as 部门, * from [F:\SQL之Excel应用\分公司].[财务部$] union allSelect “分公司2” as 公司, “市场部” as 部门, * from [F:\SQL之Excel应用\分公司].[市场部$]因为SQL中已经指定了文件名和表名，所以建立连接时连接谁并不重要，这种情况下，建立连接的时候就连接自己，然后再改写SQL语句。

2、子查询和多表连接所谓子查询就是将一个查询结果作为数据源放在主查询语句中，多表连接则是将两个有关联的表通过关键字段连接在一起查询，这都是SQL知识，不再赘述，需要注意的是，不同的数据库系统SQL都有些微小的差别，Excel中的SQL也有其自己的一些特点，关于多表查询的写法，见本文附录。

3、常用运算符有条件的查询条件是where引导的，用and、or等连接，例如：select prov_name, city_name, xs_mc, xs_codefrom [Sheet1$] where prov_name=’安徽’ or prov_name=’江苏’--虽然字符串可以用双引号，但建议用单引号，因为oracle 、SQL server都是用单引号。

常用运算符：in、not in、between … and …、is null、is not null、&（连字符）、like、not like，注意：null和任何字段运算的结果都是null。

通配符：%（所有字符或无字符）、_（单个字符）、[]（区间，如[1-9]、[!a-f]、[1,3,5]），例如：select * from[Sheet1$] where Email like ‘[h-m]%’ --h-m开头的电子邮件select * from[Sheet1$] where xs_code like '%[!1,3,5]' –和not like '%[1,3,5]'效果相同select * from[Sheet1$] where 户籍&’-’&工作地 like '%合肥%' --中间加个“-”防止误差筛选查询结果：Distinct 去重复、top n 取前n条记录聚合函数：count、sum、min、max、avg 排序：order by、分组：group by、分组后筛选：having SQL中关键字的执行顺序：from=1 where=2 group by=3 having=4 order by=5select=6，因为select在最后，所以其它关键字后面不能用字段别名，不过，表的别名是可以用的，因为from排在第一。

4、常用函数除了聚合函数，还有很多其他函数，这些函数有的是所有数据库系统都有的，有的是数据库系统特有的。

Excel中工作表中使用的函数基本都能在SQL中使用，例如：数学：abs、int、fix、round、mod、rnd、……文本：left、right、mid、len、instr、string、replace、format、……条件：iif、switch、choose、……日期：date/now、year/month/day、weekday、dateserial、……有些函数用法和工作表中略有不同，如date可以取当前日期，但是不能合成日期，合成日期用dateserial（这个函数只能在SQL中使用）5、交叉查询交叉查询产生一个透视表，相当于一个矩形二维表，这是Excel特有的查询，格式如下：Transform 聚合函数 select 行标签 from [数据表$] groupby 行标签 pivot 列标签，例如：Transform sum(工资) select 部门名称 from [员工$] group by 部门名称 pivot 职务这个语句产生的结果与数据透视表差不多，相当于一个语句产生一个数据透视表，当然这个透视表是固定的，和语句对应的。

其中的select语句，相当于数据透视表的行字段，其中的聚合函数的参数相当于拖到数据透视表数据区域的值字段，使用的聚合函数即值字段的汇总方式。

其中的 pivot字段相当于数据透视表的列字段，后面的IN (value1[, value2[, ...]])]，相当列字段中的项的排序和筛选，摆弄过数据透视表，将transform/pivot语句与数据透视表对照，可以轻松掌握这个MS JET新增SQL语句。

看一下效果：列标签筛选Transform sum(工资) select 部门名称 from [员工$] group by 部门名称 pivot 职务 in (‘主管’, ‘经理’)多个行标签Transform sum(工资) select职务,性别from [员工$] group by职务,性别pivot 部门名称如需要添加总计，则需要先构造一个子查询结果，这个结果由正常的查询和统计查询联合在一起，再以这个结果作为数据源，构成上面的二维表。

例如：Transform sum(工资) select 部门名称 from (Select 部门名称,职务,工资 from [员工$] union allSelect 部门名称,’总计’,sum(工资) from [员工$] group by部门名称) group by 部门名称 pivot 职务in (‘主管’, ‘经理,’职员’,’总计’ )6、文本型数字SQL查询时字段类型是由前8行数据决定的（这个数字是Excel定的），如果前8行都是数值型，后面有文本型数字，则查询结果中这些数字变成为空；前8行是文本型，后面是数值型则不影响，似乎查询结果偏向文本。

如果前8行中类型不一致，有数值型，也有文本型数字，可以通过在连接字符串中加入IMEX=1则后面有文本型字符也没关系，但是，如果前8行都是数值型，加了这个也不管用，因为前8行已经决定是数值型了。