数据透视表之SQL高级查询.xls

一一 子子查查询询详详讲讲 子查询的一些特别要注意的地方 数数据据透透视视表表之之SQLSQL高高级级查查询询 1 子查询必须放在括号内 2 子查询也必须放在比较操作符号的右边 3 子查询最多可以嵌套到255级 4 子查询中不能使用ORDER BY子句 即ORDER BY必须位于查询的最外层 2009 112009 11广广州州 一 单行子查询 单行子查询并不是最后输出的结果只能返回一行 而是指子查询只能返回一行 有一条规则我们必须记住 当我们用 等于 比较操作符把子查询和父查询嵌套在一起时 父查询期望从子查询那里只得到一行返回值 例例1 1 查查询询所所在在部部门门为为 研研究究B B部部 的的职职工工在在职职工工表表中中的的记记录录 select from 职工表 where 部门编号 select 部门编号 from 部门表 where 部门 研究B部 其它一些表明是单行子查询的比较操作符有 二 多行子查询 多行子查询 意味着子查询返回的结果子集可以是多行 因此 我们通常用集合比较操作符 如 IN NOT IN 把父查询和子查询连接起来 例例2 2 查查询询所所在在控控股股方方以以A A开开头头的的职职工工在在职职工工表表中中的的记记录录 select from 职工表 where 部门编号 in select 部门编号 from 部门表 where 控股方 like A 三 多重嵌套子查询 例1和例2中 主查询的WHERE子句中都只有一列 然而 有时需要主查询处理多重关系 例例3 3 查查询询每每个个部部门门工工资资最最高高的的职职工工信信息息 select from 职工表 where 部门编号 工资 in select 部门编号 工资 from select 部门编号 max 工资 as 工资 from 职工表 group by 部门编号 注意 多重嵌套查询 一般是自内向外 这样写好内层的SQL语句 再慢慢地嵌套好外层的语句 对自己的思维发散有好处 建议学员们学多重嵌套的时候 先把内部的语句复制出来测试一下 看看结果是怎么样的 一般在oracle和MS SQL中会出现多列子查询 但在excel中 只能通过嵌套和增加新的连接字段来实现多列子查询 四 带有any some 或 all 谓词的子查询 使用any 或all 谓词时必须同时使用比较运算符 主要见 any大于子查询结果中的某个值 all大于子查询结果中的所有值 any小于子查询结果中的某个值 any大于或等于子查询结果中的某个值 all大于或等于子查询结果中的所有值 any小于或等于子查询结果中的某个值 all小于或等于子查询结果中的所有值 any等于子查询结果中的某个值 all等于子查询结果中的所有值 或 any 不等于子查询结果中的某个值 或 all 不等于子查询结果中的任何一个值 例例4 4 查查询询其其他他部部门门编编号号中中比比部部门门编编号号为为A0003A0003的的某某一一职职工工的的工工资资还还要要少少的的所所有有记记录录 select from 职工表 where 工资 any select 工资 from 职工表 where 部门编号 A0003 and 部门编号 A0003 子查询工资集 首先子查询 找出部门编号为A0003 中所有职工的工资 构成一个集 合 注意 该查询结果也可以用聚集函数来实现 首先用子查询找出部门编号为A0003中最高工资为6500 然后在父查询中查出所有部门编号不是A0003且工资小于6500的职工记录 SQL语句如下 select from 职工表 where 工资 select max 工资 from 职工表 where 部门编号 A0003 and 部门编号 A0003 例例5 5 查查询询其其他他部部门门编编号号中中比比部部门门编编号号为为A0001A0001的的所所有有职职工工的的工工资资还还要要少少的的所所有有记记录录 select from 职工表 where 工资 all select 工资 from 职工表 where 部门编号 A0001 and 部门编号 A0001 注意 该查询结果也可以用聚集函数来实现 首先用子查询找出部门编号为A0001中最低工资为1300 然后在父查询中查出所有部门编号不是A0001且工资小于1300的职工记录 SQL语句如下 select from 职工表 where 工资 select min 工资 from 职工表 where 部门编号 A0001 and 部门编号 A0001 注意 用聚集函数查询通常要比any 和 all查询的效率要高 特别是大数据量的时候就比较明显的 any 或 some all 谓词与聚集函数 in谓词的等价转换关系 或 anyin max all not in b 基本工资 结果表 有一个成绩表 里面有学号 功课编号 学生成绩三个字段 例例7 7 用用一一个个SQLSQL查查询询语语句句得得出出每每门门功功课课成成绩绩最最好好的的前前两两名名 select distinct a from 成绩表 a where a 学号 in select top 2 成绩表 学号 from 成绩表 where 成绩表 功课编号 a 功课编号 order by 学生成绩 desc 注意 成绩表 a 这里的a起到中转站的作用 可以简化新表的缩写 结果表 内部的子连接 首先对学生成绩进行降序排列 然后提取最好成绩的前2名 select top 2 成绩表 学号 from 成绩表 where 成绩表 功课编号 a 功课编号 order by 学生成绩 desc 值得注意的是 因为是要查询每门功课 故外部一层与内层的联系是功课编号要相等 便出现了 成绩表 功课编号 a 功课编号 而外面一层主要是为了提取其记录 需要不重复的 定义了一个新的虚构表 成绩表 a 而功课编号为连接条件进行连接 4 子查询中不能使用ORDER BY子句 即ORDER BY必须位于查询的最外层 单行子查询并不是最后输出的结果只能返回一行 而是指子查询只能返回一行 当我们用 等于 比较操作符把子查询和父查询嵌套在一起时 父查询期望从子查询那里只得到一行返回值 例例1 1 查查询询所所在在部部门门为为 研研究究B B部部 的的职职工工在在职职工工表表中中的的记记录录 select from 职工表 where 部门编号 select 部门编号 from 部门表 where 部门 研究B部 其它一些表明是单行子查询的比较操作符有 多行子查询 意味着子查询返回的结果子集可以是多行 因此 我们通常用集合比较操作符 如 IN NOT IN 把父查询和子查询连接起来 select from 职工表 where 部门编号 in select 部门编号 from 部门表 where 控股方 like A 例1和例2中 主查询的WHERE子句中都只有一列 然而 有时需要主查询处理多重关系 select from 职工表 where 部门编号 工资 in select 部门编号 工资 from select 部门编号 max 工资 as 工资 from 职工表 group by 部门编号 然后进行父查询 找出所有部门编 号不是A0003且工资少于6500或其 以下的职工记录 注意 多重嵌套查询 一般是自内向外 这样写好内层的SQL语句 再慢慢地嵌套好外层的语句 对自己的思维发散有好处 建议学员们学多重嵌套的时候 先把内部的语句复制出来测试一下 看看结果是怎么样的 一般在oracle和MS SQL中会出现多列子查询 但在excel中 只能通过嵌套和增加新的连接字段来实现多列子查询 使用any 或all 谓词时必须同时使用比较运算符 主要见 例例4 4 查查询询其其他他部部门门编编号号中中比比部部门门编编号号为为A0003A0003的的某某一一职职工工的的工工资资还还要要少少的的所所有有记记录录 select from 职工表 where 工资 any select 工资 from 职工表 where 部门编号 A0003 and 部门编号 A0003 min min max max 首先用子查询找出部门编号为A0003中最高工资为6500 然后在父查询中查出所有部门编号不是A0003且工资小于6500的职工记录 select from 职工表 where 工资 select max 工资 from 职工表 where 部门编号 A0003 and 部门编号 A0003 例例5 5 查查询询其其他他部部门门编编号号中中比比部部门门编编号号为为A0001A0001的的所所有有职职工工的的工工资资还还要要少少的的所所有有记记录录 select from 职工表 where 工资 all select 工资 from 职工表 where 部门编号 A0001 and 部门编号 A0001 首先用子查询找出部门编号为A0001中最低工资为1300 然后在父查询中查出所有部门编号不是A0001且工资小于1300的职工记录 select from 职工表 where 工资 select min 工资 from 职工表 where 部门编号 A0001 and 部门编号 A0001 注意 用聚集函数查询通常要比any 和 all查询的效率要高 特别是大数据量的时候就比较明显的 any 或 some all 谓词与聚集函数 in谓词的等价转换关系 五五 自自连连接接 比比较较难难理理解解 给给有有基基础础的的学学员员去去研研究究 课课上上不不讲讲 例例子子6 6 查查询询工工资资表表中中员员工工的的姓姓名名和和基基本本工工资资 其其中中每每行行包包含含两两组组员员工工的的姓姓名名和和基基本本工工资资 且且左左边边的的员员工工工工资资要要大大于于右右边边员员工工的的工工资资 select a 员工姓名 a 基本工资 b 员工姓名 b 基本工资 from 工资表 a inner join 工资表 b on a 基本工资 b 基本工资 有一个成绩表 里面有学号 功课编号 学生成绩三个字段 select distinct a from 成绩表 a where a 学号 in select top 2 成绩表 学号 from 成绩表 where 成绩表 功课编号 a 功课编号 order by 学生成绩 desc 注意 成绩表 a 这里的a起到中转站的作用 可以简化新表的缩写 内部的子连接 首先对学生成绩进行降序排列 然后提取最好成绩的前2名 select top 2 成绩表 学号 from 成绩表 where 成绩表 功课编号 a 功课编号 order by 学生成绩 desc 值得注意的是 因为是要查询每门功课 故外部一层与内层的联系是功课编号要相等 便出现了 而外面一层主要是为了提取其记录 需要不重复的 定义了一个新的虚构表 成绩表 a 员工部门编号职位工资 ChrisA0001研究经理20000 RitaA0002研究员3500 IreneA0003研究员3800 CalvinA0004研究员3700 SteveA0005助理研究员2000 KevinA0002高级研究员9600 AmyA0003助理研究员1500 BruceA0002高级研究员5500 MaggieA0003高级研究员6500 UraniaA0004助理研究员1200 BenA0005助理研究员2300 VickyA0003研究员3300 JohnA0004助理研究员1100 SnoopyA0005研究员3300 VincentA0002研究经理19000 PonyA0003研究员3000 GingerA0002助理研究员1000 EllaA0001助理研究员1300 BlindaA0002助理研究员1000 KenA0003高级研究员5500 AliceA0004研究员2400 EricA0005研究员3300 JackyA0002研究员3400 MaryA0003助理研究员1000 CooperA0004助理研究员1000 JonnaA0005研究员3000 BillA0002研究总监100000 WiseA0003高级研究员6000 JoheA0002研究经理18000 CandyA0002高级研究