SQL语言在数据透视表中的应用.doc

SQL语言在透视表中的应用目 录一、Select入门：1实例应用（单表查询）81. 查询财经、计算机专业的学生情况82. 查询成绩在80-95之间的学生情况93. 查询前六位学生成绩排名94. 计算增加学生的年龄字段95. 统计财经系的男女人数106. 按照性别，学部查询学生人数117. 查找选修2门以上课的学生118. 查询选修课两门以上，并且成绩在85分以上的学生129. 字段名称查询约定12不规则表查询：13二、Select进阶：18（一）联合查询(合并查询)18例:依据学号查询语文和物理成绩18多表列不相等的数据合并查询18（二）嵌套查询20（1） 查询“张三”选修的课程和成绩21（2）查询“张三”选修的语文课和成绩21嵌套查询中的子查询详解：22（1）单行子查询（简单嵌套查询）22（2）多行子查询（带in的嵌套查询）22（3）多重嵌套子查询23（4）带有any(some) 或 all 谓词的子查询23（5）带(not) exists的嵌套查询25（6）自连接27（三）多表查询（关联查询）311、 依据学号条件查询学生的各门成绩322、依据学号条件查询学生的各门成绩大于85分32综合示例32(1)查询员工的基本工资32表名和别名使用规则：33(2)联合查询每个部门每个员工对应的基本工资34(3)查询每个部门每个员工的销售总额34(4)查询每个产品的销售情况353、各班级干部的第一名的记录364、进出库的综合查询36（四）对于同一表格的分散数据如38（五）同一表格多重数据39（六）transform 交叉查询40语法：40语法详解：42null在transform中的应用51（七）格式转换51三、where子句查询条件521、运算符532、Or 与 And543、BetweenAnd544、IN ，NOT IN545、 Like55四、SQL语言的select命令基本格式57查询语句各个部分的执行顺序:59select命令的几个说明59SQL查询特性64SQL语句多表联接的几种形式65常见问题及解答66五、其他功能查询681、Select Into和Insert Into682、Update 语句693、DELETE 语句694、INSERT INTO 语句705、生成表查询72六、基于Mcrosoft Query（MQ）查询721、MQ参数查询：752、使用MQ可以自动更新77七、VBA书写SQL语句方法79附：各Excel版本使用SQL的步骤与注意事项83（一）方法步骤83（二）注意事项87参考网址： ttp://sql/index.asp http:/club.E/thread-607261-1-4.html http:/www.E/post-6693-232-lastpage.html http:/club.E/thread-548652-1-1.html/forum.php?mod=viewthread&tid=2141&extra=page%3D1一、Select入门： “数据源”表：日期分钟小时番号出货数检查数不良数406341202400651421420406345409400666166261040635300.54006131354406351202400661281492140635751.2540062616324063960193657197201440639360693657114011713140640120293657263268540640360643010117612224640641300543010819830114064224044301080482521406452704.54006129931112406456014006261610406463305.5400623893967406461502.540065196198240647240440065256263740647240440066186186040647601430101301322406476019365797981406661202999991231230409091803123452344321981、如提取字段名为”日期”,”出货数”,”检查数”的列的记录(从名为”数据源”表),请使用类似这样的select语句：select 日期,出货数,检查数 from 数据源$如提取所有列的记录，这样一个列名一个列名写有点麻烦，那怎么办呢，其实可以用*，这个相当于所有列的记录：select * from 数据源$2、如果只想提取番号是”40056”的所有记录怎么办，这时就需用where。where语法：where 筛选条件select * from 数据源$ where 番号=400653、如果仅想提取两个番号的记录呢，这时就得用”in(番号1，番号2)”；IN是运算符，判断表达式的项是否在指定的集合中。select * from 数据源$ where 番号in (40065,40066)4、如果想要得到番号40065和出货数196的所有记录，这时就得用and，把筛选条件连起来；select * from 数据源$ where 番号 = 40065 and 出货数 = 1965、如想筛选出数量在某个区间的所有项目，这时就得用 “between 起始数量 and 结束数量”，用以指定范围；如：筛选出出货数在200到500之间的记录select * from 数据源$ where 出货数 between 200 and 5006、假如不想筛选出数量在某个区间的所有项目，这时就得用”not between 起始数量 and 结束数量”；如：筛选出货数不在200到500的数量select * from 数据源$ where 出货数 not between 200 and 5007、有的时候番号很多，但其中有很多是重复的，那怎么去重复项目，只保留不重复呢，这时就得使用”distinct 番号”，distinct就是去重复的。select distinct 番号 from 数据源$输入语句后就得到所有不重复的番号，效果如下：8、如果要对数据进行排序，必须用order by，语法：order by 字段 desc order by 字段 asc（或省略）asc（升序），默认情况下sql都是升序的，可以省略不写；desc（降序）。如果想要把不重复的番号进行降序排序就可以使用下面的语句select distinct 番号 from 数据源$ order by 番号 desc9、插入计算字段：在普通的透视表中可以用插入计算字段对透视表进行运算，用sql同样可以做到。如：汇总每个番号的不良数（即检查数-出货数）select 番号,检查数-良品数 as 不良数 from 数据源$10、聚合函数。excel最有魅力的地方是函数，那么在sql中可以使用函数吗？怎么使用？下面通过简单的例子来了解sql中的函数。如，求总出货数(对某个字段进行求和):select sum(出货数) from 数据源$求平均出货数：select avg(出货数) from 数据源$求出货批数：select count(出货数) from 数据源$看到这里，问题来了，怎么求和，平均数，计数上面都有Expr1000？，又该怎么换成自己需要的字段名呢？ Expr1000其实是sql默认的字段名，完全可以自定义成任意想展现的字段，这时就得使用”as”，具体如下：select count(出货数) as 批数 from 数据源$效果如下：如果想知道每种番号的最大出货数，这时就得用”group by” 来进行分组了。select 番号,max(出货数) as 最大出货数 from 数据源$ group by 番号相反，如果想求每种番号的最小出货数，就可以这样写：select 番号,min(出货数) as 最小出货数 from 数据源$ group by 番号再来一个难度大一点点的，求良品率（就是总良品数/总检查数）。这样就需要先对数据进行求和（sum），因为使用聚合函数，就需要对字段进行分组（group by）select 番号,sum(良品数)/sum(检查数) as 良品率 from Sheet1$ group by 番号注：这种实际用插入计算字段更简单，只是为了说明sql语句而已如果想得到每个番号的最大值且最大值大于300的所有项目怎么办，也许有人会说用：select 番号,max(出货数) as 最大出货数 from 数据源$ group by 番号 where max(出货数) 300前面说到，where可以进行筛选出想要的结果，不过在这里就不合适了，会提示出错。既然这样，那有什么办法解决呢？使用了聚合函数，就得用having。其实两者都用于按条件筛选，只是一个用于分组前，一个用于分组后。分组后按一定条件对这些组筛选，则可以在group by 子句后，加上having 短句来指定筛选条件。注意：having短句通常和group by 子句结合使用。select 番号,max(出货数) as 最大出货数 from 数据源$ group by 番号 having max(出货数) 300假如想得到番号以”400”开头的总出货数，在excel函数中表示多个字符用*，一个字符用？，那在sql中是不是一样呢？其实sql中有自己的一套表示方法，“%”代表多个字符，“_”代表单个字符，所以400开头的可以这样表示like 400%，具体如下select 番号,sum(出货数) as 总出货数 from 数据源$ group by 番号 having 番号 like 400%聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 select 语句的 group by 子句一同使用。（1）SQL聚合函数语法：SELECT 函数名(列名) FROM 表名$（2）聚合函数中,sum、count、avg均忽略空值,当使用group by分组查询时,所有的聚合函数均忽略空值。（3）COUNT(*)是统计表的行记录，COUNT(字段）是统计表指定字段的记录。（4）聚合函数中的参数可以是字段名、函数和常量，但不能是其他聚合函数。（5）聚合函数除列举的求和、计数、平均值、首次、末次，还包括标准偏差和标准方差。即：sum、Count、Avg、First、Last、Min、Max、StDev、StDevP、Var、VarPSQL聚合函数函数名功能COUNT返回指定组中项目的数量。AVG返回指定组中的平均值，空值被忽略。SUM返回指定数据的和，只能用于数字列，空值被忽略。MIN返回指定数据的最小值。MAX返回指定数据的最大值。First返回第一条记录中的指定值Last返回最后一条记录中的指定值StDev返回给定表达式中所有值的统计标准偏差。StDevP返回给定表达式中的所有值的填充统计标准偏差。Var 返回给定表达式中所有值的统计方差。VarP返回给定表达式中所有值的填充的统计方差。（6）分组查询通常与SQL聚合函数一起使用，先按指定的数据项分组，再对各组计算。如果不分组，则聚合函数将作用整个查询结果。实例应用（单表查询）表名：学生序号姓名年龄性别平均成绩学部1张三22女85.6财经2李四21男72.5网络技术应用3王五18男90计算机4马六19女100财经5周七23男92.5多媒体技术6丽萍17女75.5网络技术应用7海华18男65网络技术应用8方海20女92财经9凯丽17女77多媒体技术10李阳峰20男85财经11蒋海坡19男90网络技术应用12蔡明波22男95多媒体技术13姜大雨19男87计算机14马 华21女99多媒体技术15呈浩歌19男100计算机1. 查询财经、计算机专业的学生情况 select 姓名,性别,平均成绩,学部 from 学生$ where 学部 in (“财经”,”计算机”)2. 查询成绩在80-95之间的学生情况 select * from 学生$ where 平均成绩 between 80 and 95 order by 姓名,平均成绩 desc3. 查询前六位学生成绩排名 select top 6 * from 学生$ order by 平均成绩 desc 4. 计算增加学生的年龄字段 表名：出生序号姓名出生性别平均成绩学部1张三1980年2月12日女85.6财经2李四1980年3月1日男72.5网络技术应用3王五1982年2月12日男90计算机4马六1978年1月23日女100财经5周七1979年1月24日男92.5多媒体技术6丽萍1980年1月25日女75.5网络技术应用7海华1982年1月26日男65网络技术应用8方海1981年1月27日女92财经9凯丽1978年1月28日女77多媒体技术10李阳峰1983年1月29日男85财经11蒋海坡1978年1月30日男90网络技术应用12蔡明波1984年1月31日男95多媒体技术13姜大雨1981年2月1日男87计算机14马 华1982年2月2日女99多媒体技术15呈浩歌1987年2月3日男100计算机select 姓名,性别,(year(date()-year(出生) as 年龄 from 出生$5. 统计财经系的男女人数 select 性别,count(*) as 人数 from 出生$ where 学部=”财经” group by 性别6. 按照性别，学部查询学生人数select 性别,学部，count(*) as 人数 from 出生$ group by 性别,学部求和项：人数学部性别汇总财经男1女3多媒体技术男2女2计算机男3网络技术应用男3女1总计157. 查找选修2门以上课的学生 表名：选修姓名性别平均成绩学部张三女85.6财经张三女72.5网络技术应用程七男90计算机马六女100财经周七男92.5多媒体技术丽萍女75.5网络技术应用丽萍女65网络技术应用方海女92财经凯丽女77多媒体技术李阳峰男85财政李阳峰男90网络技术应用蔡明波男95多媒体技术蔡明波男87计算机蔡明波男99多媒体技术呈浩歌男100计算机select 姓名,性别 from 选修$ group by 姓名,性别 having count(*) =2计数项：姓名姓名性别汇总张三女1蔡明波男1李阳峰男1丽萍女1总计48. 查询选修课两门以上，并且成绩在85分以上的学生 select 姓名 from 选修$ where 平均成绩=85 group by 姓名 having count(*)=2计数项：姓名姓名汇总蔡明波1李阳峰1总计29. 字段名称查询约定假如字段名称被强制换行，那么，生成的字段名称会有一个“_”，“_”为强制换行的位置；假如字段名称含有空格或特殊字符，会被自动替换成相应的字符；且必须用“”或“”括住字段名称。在写SQL语句时，字段名称使用的是更正后的字段名称，而不是数据源表的列标题。当字段名称是空或字段名称以数字开头，SQL会自动的将该字段的名称更正为Fx。其中，x为该字段在表中的位置。 文本用双引号或单引号表示，如：测试1，可以表示为： 测试1，也可以表示为 测试1；路径和表用中括号或重音符区别。如路径D:连接测试测试1，可以表示为： D:连接测试测试1.xls.表名$，也可以表示为D:连接测试测试1.xls .表名$。重音符与波浪符处于同一个键，位于波浪符之下 (“1”左边那个键)。不规则表查询：例1：表名：数据源区域门店.业务员1月2月数量金额数量金额白云白云第3分店李泽坚10528353005100白云白云第4分店张德培235110454207560黄埔黄埔第2分店黄泽处15600黄埔黄埔第5分店李明爱195234047018330黄埔黄埔第7分店黄泽处340136001052205荔湾荔湾第3分店张楚3453450荔湾荔湾第3分店陈想妹2024028013160荔湾荔湾第4分店张楚天河天河第2分店张建中30012001252500天河天河第3分店陈红458102507000天河天河第6分店陈红妹12562502054715越秀越秀第5分店李毅建130143046017940越秀越秀第7分店陈东建35136545018900要求（返回表）：1、找出2月金额比1月金额小的记录（说明：结果表返回所有字段）。select _区域 as 区域,_门店# as 门店,_业务员 as 业务员,1月 as 1月,F5 as 1月金额,2月 as 2月,F7 as 2月金额 from 数据源$A1:G15 where IIf(isnull(f7),0,f7)，=，=，”。（2）多行子查询（带in的嵌套查询）多行子查询，意味着子查询返回的结果子集可以是多行。因此，我们通常用集合比较操作符（如：IN, NOT IN）把父查询和子查询连接起来。例 ：查询得到雇员表里满足薪水符合姓名为张三的薪水的所有雇员的相关信息select 雇员表.雇员编号, 雇员表.姓名, 雇员表.职位, 雇员表.薪水 from 雇员表 where 薪水 in (select 薪水 from 雇员表 where 姓名=张三)注：此语句完成的查询薪水和张三相等的职员，也可以使用not in来进行相反的查询。（3）多重嵌套子查询上面两例中，主查询的WHERE子句中都只有一列。然而，有时需要主查询处理多重关系。例：查询每个部门工资最高的职工信息select * from 职工表$ where 部门编号 & - & 工资 in ( select 部门编号 & - & 工资 from (select 部门编号,max(工资) as 工资 from 职工表$ group by 部门编号)（4）带有any(some) 或 all 谓词的子查询使用any 或all 谓词时必须同时使用比较运算符。谓词说明1等价于备注:ANY(SOME)大于子查询里的某一个值min()1、当要比较的值只有一个时，可以直接用、=、2、使用聚合函数实现子查询通常比ANY/ALL等谓词高效,因为使用聚合函数可以减少比对次数。=ANY(SOME)等于子查询内的某一个值in=ANY(SOME)大于等于子查询内的某一个值=min()ANY(SOME)小于子查询内的某一个值max()=ANY(SOME)小于等于子查询内的某一个值ALL大于子查询的所有值max()=ALL等于子查询的所有值通常无意义=ALL大于等于子查询所有值=max()ALL小于子查询的所有值min()=ALL小于等于子查询内的所有值=min()ALL不等于子查询内的所有值NOT IN带有any(some)的嵌套查询any与some同义，表示某些值。语法：select 字段 from 表1$ where 字段 /=/= any(some) (select 字段 from 表2$)表名：成绩表学校班级学生语文数学英语总分甲校甲01-1陈红884369200甲校甲01-1张楚884369200甲校甲01-1李静888080248甲校甲02-1郭澄城884369200甲校甲02-1李海885080218甲校甲02-1黄涛884369200甲校甲02-2郑成功884369200甲校甲02-2黄绍裘884369200乙校乙01-1黄冈624369174乙校乙01-1李京鸿614369173乙校乙01-1张继聪6769136乙校乙02-1黄车714369183乙校乙02-1蒋狐猴894150180乙校乙02-1李浩天969842236乙校乙02-2满天星634369175乙校乙02-2李浩东954369207丙校丙01-1张长城994369211丙校丙01-1郭爱为709790257丙校丙01-2郭晶精975865220丙校丙01-2河田774457178查找总分比甲校某些学生的总分要高的记录。 select * from 成绩表$ where 总分ANY(select 总分 from 成绩表$where 学校=甲校) 注：用聚合函数可以这样表示：select * from 成绩表$ where 总分(select min(总分) from 成绩表$ where 学校=甲校)查询数学与甲校数学成绩相等的记录。select * from 成绩表$ where 数学=any(select 数学 from 成绩表$where 学校=甲校) 上语句等价于：select * from 成绩表$ where 数学 in (select 数学 from 成绩表$where 学校=甲校) 带all的嵌套查询语法：select 字段 from 表1$ where 字段 /=/= ALL (select 字段 from 表2$ )例1:假如我们需要获得语文成绩比数学成绩高的记录,那么,我们可以输入:select * from 成绩表$a where 语文all (select 数学 from 成绩表$ WHERE A.学校=学校 and A.班级=班级 and A.学生=学生)注：在这里，由于没有对应学生的唯一ID，我们只能从条件里限制。实际中，学生是有学生ID作为唯一对应的。即使，使用上面的语句，只是减少出错，不能完全排除出错。如：当同一学校同一班级有多名姓名重复的学生时，结果就会出错。扩展：假设，同一学校同一班级不存在姓名重复的学生，那么，上面的语句的结果就不会出错。也可以简化为：select * from 成绩表$a where 语文all (select 数学 from 成绩表$ WHERE A.学校&A.班级&A.学生=学校&班级&学生)A.学校&A.班级&A.学生和学校&班级&学生，相当于构建一个新的字段，这个字段的作用相当于学生的ID为避免字段组合时，出现重复情况，如下表的情况。那么，我们可以加表达式来杜绝。如：字段1&-&字段2字段1字段2字段1&字段2字段1&-&字段2ABCABCAB-CABCABCA-BC例2：查询总分比总分比总分平均分高的学生记录。select * from 成绩表$ where 总分all (select avg(总分) from 成绩表$)注：由于聚合函数的存在，使得主查询要比对的值只有一个，所以，这里我们直接使用比较运算符，即：select * from 成绩表$ where 总分= (select avg(总分) from 成绩表$)例3：查询总分比甲校总分要高的学生记录。select * from 成绩表$ where 总分ALL (select 总分 from 成绩表$where 学校=甲校)注：all谓词表示所有的，all可以理解为返回主查询中比子查询所有值都要大的值，即相当于返回主查询中比子查询最大值还要大的值。等效于：select * from 成绩表$ where 总分(select MAX(总分) from 成绩表$where 学校=甲校)（5）带(not) exists的嵌套查询带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。如子查询为空，则false,主查询不返回记录； 子查询不为空，则true,主查询返回记录。 查询过程：通过将主查询与子查询作比较，产生true/false逻辑值，确定查询是否返回记录。由于子查询只返回逻辑值，而不是返回结果集，子查询使用字段名称无实际意义。not Exists：将比对结果相反 （注：通常(not)Exists效率比in高）（1）select * from 成绩表$ where exists (select * from 成绩表$)注：子查询（select * from 成绩表$）为TURE，返回主查询（select * from 成绩表$）（2）select * from 成绩表$ where exists (select * from 成绩表$ where 语文90)注：子查询（select * from 成绩表$ where 语文90）有返回值，产生true,则返回主查询所有记录。（3）select * from 成绩表$ where not exists (select * from 成绩表$ where 语文90)注：子查询（select * from 成绩表$where 语文90）有返回值，子查询产生true，加上not,则为false,不返回主查询（select * from 成绩表$）的记录。（4）select * from 成绩表$a where exists (select * from 成绩表$ where a.语文90)注：子查询（select * from 成绩表$ wher