sql数据库数据的查询、汇总、统计和分析.pdf

《sql数据库数据的查询、汇总、统计和分析.pdf》由会员分享，可在线阅读，更多相关《sql数据库数据的查询、汇总、统计和分析.pdf（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、8.数据的查询、汇总、统计和分析 本章主题 SELECT 语句 简单的 SELECT 查询 设置查询结果的字段名 关键字 ALL 和 DISTINCT 的使用 查询结果的输出目的地 WHERE 子句的条件搜索功能 SELECT 的通配符 字符和通配符冲突时的解决方法 连接条件设置 统计运算的高手：聚合函数 数据分组小计 HAVING 子句的使用 ORDER BY 子句的使用 查询名列前茅或落后者 活用子查询（SubQuery）精彩内容不容错过！这一章是本课程的重点！本章将深入剖析 SELECT 命令。8.1.SELECT 命令 SELECT 是一个用来从一个或多个表中获取数据的 SQL 命令。

2、8.2.简单的 SELECT 查询 如：USE NorthwindSQL SELECT 身份证号码,姓名,电话号码 -这里是字段列表 FROM 飞狐工作室 SELECT 命令至少包含：要出现在查询结果中的字段列表，如：身份证号码，姓名，电话号码 字段来自哪些表，如：FROM 飞狐工作室 字段列表也可以是由字段、常量和函数组成的表达式 要列出所有字段，只须用*号 8.3.设置查询结果的字段名 查 询 需 求 请从数据库 pubs 的 authors 表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。解 答 /*脚本文件名:*/USE pubs SELECT

3、 作者代号=au_id,姓名=au_fname+au_lname,电话号码=phone,住址=address FROM authors 或（看看你更喜欢哪一种格式）/*脚本文件名:*/USE pubs SELECT au_id AS 作者代号,au_fname+au_lname AS 姓名,phone AS 电话号码,address AS 住址 FROM authors 注意：如果您设置的的字段名包含空格，则须加上单引号 SELECT au_fname+au_lname AS Name of Author FROM authors 任务：查询 teacher 表的姓名、性别和生日，列名用中文表

4、示；查询 class 表的所有班级信息；查询 teacher 表的教师姓名和年龄。8.4.关键字 ALL 和 DISTINCT 的使用 1.查询需求 请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。USE NorthwindSQL SELECT DISTINCT 雇用日期 FROM 飞狐工作室 2.查询需求 请列出“飞狐工作室”表中有哪些部门。USE NorthwindSQL SELECT DISTINCT 部门 FROM 飞狐工作室 ALL 为默认项，显示所有查询到的记录，包括重复项。DISTINCT，对指定字段的内容相同的，仅显示一项。每个 SELECT 表达式只

5、能有一个 DISTINCT 关键字。这意味着，DISTINCT 是限制整条数据记录都重复者，只显示其中一条，而不是针对单一字段来处理。任务：请列出“章立民工作室”表中有哪些部门。列出 teacher 表中的教师职称。8.5.查询结果的输出目的地 为什么要讨论查询结果的输出目的地 答：可能希望将查询结果输出到某个存储处以便进行进一步的处理。举例说明 INTO 子句的用法：将查询结果存储到当前数据库中的新表 MyTmpTable 中 USE NorthwindSQL SELECT *INTO MyTmpTable FROM 飞狐工作室 注：INTO 子句会生成相应的表，如果表已存在则提示错误“表已

6、存在”。任务：把 teacher 表中的教师职称保存到新表教师职称表中。查询员工表中的职称，并存储到新表员工职称表中。8.6.WHERE 子句的条件搜索功能 1.查询需求 请列出“飞狐工作室”表中目前薪资大于 60000 的员工。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,目前薪资 FROM 飞狐工作室 WHERE 目前薪资 60000 2.查询需求 请列出“飞狐工作室”表在信息部、行销部和业务部等 3 个部门任职的员工姓名。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,部门 FROM 飞狐工作室 WHERE 部门 IN(资讯部,行销部

7、,业务部)3.查询需求 请列出“飞狐工作室”表在信息部、行销部和业务部等 3 个部门以外任职的员工姓名。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,部门 FROM 飞狐工作室 WHERE 部门 NOT IN(资讯部,行销部,业务部)4.查询需求 请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,出生日期 FROM 飞狐工作室 WHERE MONTH(出生日期)=MONTH(GETDATE()5.查询需求 请列出“飞狐工作室”表中，年龄大于 20 岁的每一位员工的姓名。/*脚本文件名:*

8、/USE NorthwindSQL SELECT 姓名 FROM 飞狐工作室 WHERE DATEDIFF(year,出生日期,GETDATE()20 任务：查询章立民工作室中已婚的员工信息；查询“飞狐工作室”表中行销部的员工信息；查询 06010111 班或者 07010211 班的学生信息；查询成绩在 80 分以下的学生选课信息；查询年龄超过 50 岁的女教师信息；查询出生日期在 1970 到 1980 年之间的教师信息。8.7.SELECT 的通配符 共有 5 个通配符。*（星 号）用于字段列表，代表源表中的所有的字段 /*脚本文件名:*/USE NorthwindSQL -连接两张表“

9、客户”表和“订货主档”表 SELECT*FROM 客户 INNER JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号 WHERE 订货主档.订单日期 BETWEEN 08/01/1996 AND 08/31/1996 /*脚本文件名:*/USE NorthwindSQL SELECT 客户.公司名称,订货主档.*FROM 客户 INNER JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号 WHERE 订货主档.订单日期 BETWEEN 08/01/1996 AND 08/31/1996%（百 分 号）只能用在 WHERE 子句中，代表 0 个或 0 个以上的字符。如：A

10、BC%代表 ABC 开头的字符串。百分号通常与运算符 LIKE 搭配使用。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE%光%-表示查询姓名中包含“光”字的记录 任务：查询“飞狐工作室”表中住在北京市的的员工信息；查询教师表中姓杜的教师信息；查询 professional 表中专业名称包含计算机的专业信息。_（下 划 线）只能用在 WHERE 子句中，代表 1 个字符。如：_A%代表第二个字符为 A 的字符串。下划线通常与运算符 LIKE 搭配使用。USE NorthwindSQL SELECT 姓名 FROM 飞狐工

11、作室 WHERE 姓名 LIKE _建_ （中 括 号）只能用在 WHERE 子句中，用来限定任何一个单个字符介于指定的范围或集合中。通常与运算符 LIKE 搭配使用。/*脚本文件名:*/USE pubs SELECT au_fname,au_lname FROM authors WHERE au_lname LIKE P-Zinger -表示第一个字符为 PZ 之间且后五个字符为 inger /*脚本文件名:*/USE NorthwindSQL SELECT 姓名 FROM 飞狐工作室 WHERE 姓名 LIKE ACD 张李%-表示什么 ORDER BY 姓名 任务：查询学号尾号为 1、3

12、、5、7、9 的学生信息。（中 括 号 中 包 含 号）只能用在 WHERE 子句中，用来限定任何一个单个字符不介于指定的范围或集合中。通常与运算符 LIKE 搭配使用。/*脚本文件名:*/USE pubs SELECT au_fname,au_lname FROM authors WHERE au_fname LIKE H-Kichel -首字母不介于 HK 之间且后为 ichel /*脚本文件名:*/USE NorthwindSQL SELECT 身份证号码,姓名 FROM 飞狐工作室 WHERE 身份证号码 LIKE ALM%-这个表示什么意思 任务：查询学号尾号不为 1、3、5、7、9

13、 的学生信息。8.8.字符和通配符冲突时的解决方法 可以使用 ESCAPE 子句通知 SQL Server 哪一个字符是常量字符而并非通配符。用专业术语来说，ESCAPE 子句所指定的字符称为“转义符”。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,家庭地址 FROM 飞狐工作室 WHERE 家庭地址 LIKE%_%ESCAPE -通知符号后的字符并非通配符，即查询住址中带下划线的记录 8.9.连接条件设置 本节内容太重要了！看如下代码，如果 TableA 有 M 条记录，TableB 有 N 条记录，则查询结果共有 MN 条记录：SELECT*FROM Table

14、A，TableB 或 SELECT*FROM TableA CROSS JOIN TableB 为了避免出现上述情况，有 4 种连接类型可以选择：INNER JOIN 特点：查询结果仅包含连接表中彼此相对应的数据记录。/*脚本文件名:本例用于查看每一位客户的订货情况 但是，并未下订单的客户不会出现在查询结果中！*/USE NorthwindSQL SELECT 客户.客户编号,客户.公司名称,客户.联系人,客户.电话,订货主档.订单号码,订货主档.订单日期,订货主档.要货日期,订货主档.送货日期,订货主档.送货方式,订货主档.运费,订货主档.收货人,订货主档.送货地址 FROM 客户 INNE

15、R JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号 更复杂的连接3 个表的例子：/*脚本文件名:*/USE NorthwindSQL SELECT 客户.客户编号,客户.公司名称,订货主档.订单号码,订货主档.订单日期,订货明细.产品编号,订货明细.单价,订货明细.数量,订货明细.折扣 FROM 客户 INNER JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号 INNER JOIN 订货明细 ON 订货主档.订单号码=订货明细.订单号码 任务：查询下了订单的客户信息：客户编号、公司名称、订单号码、订单日期；查询产品信息：产品编号、类别名称；查询学生信息：学号、姓名、

16、班级名称；查询班级信息：班级编号、班级名称、专业名称；查询专业信息：专业编号、专业名称、系名称；列出计算机工程系的专业信息；查询孙晓龙的所有选修课成绩；查询选修了“3dsmax”课程的学生姓名和课程成绩。LEFT OUTER JOIN（左外连接）特点：查询结果将包含位于关键字 LEFT OUTER JOIN 左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。/*脚本文件名:本例用于查看每一位客户的订货情况 但是希望并未下订单的客户也出现在查询结果中！*/USE NorthwindSQL SELECT 客户.客户编号,客户.公司名称,客户.连络人,订货主档.订单号码,订货主档.收货人

17、 FROM 客户 LEFT OUTER JOIN 订货主档 ON 客户.客户编号=订货主档.客户编号（加上条件限制试试 WHERE 订货主档.订单号码 is NULL）RIGHT OUTER JOIN（右外连接）特点：查询结果将包含位于关键字 RIGHT OUTER JOIN 右侧源表中的所有数据记录，但是仅包含左侧源表中相应的数据记录。/*脚本文件名:查看每一种产品的销售情况 但是希望那些没有人订购的产品数据也在查询结果中出现*/USE NorthwindSQL SELECT 订货明细.订单号码,订货明细.单价,订货明细.数量,订货明细.折扣,产品资料.产品编号,产品资料.产品 FROM 订

18、货明细 RIGHT OUTER JOIN 产品资料 ON 订货明细.产品编号=产品资料.产品编号（加上条件限制试试 WHERE 订货明细.订单号码 is NULL）FULL OUTER JOIN（全外连接）特点：查询结果将包含位于关键字 FULL OUTER JOIN 左右两侧源表中的所有数据记录。/*脚本文件名:给表指定别名*/USE NorthwindSQL SELECT a.客户编号,a.公司名称,a.连络人,a.电话,b.订单号码,b.订单日期,b.要货日期,b.送货日期,b.送货方式,b.运费,b.收货人,b.送货地址 FROM 客户 a INNER JOIN 订货主档 b ON a

19、.客户编号=b.客户编号 小技巧：给表指定一个较短的别名，最大的好处是缩减了SELECT 语句的长度。范例：1.查询在 1996 年 7 月份采购的北京市客户的基本数据、订单数据、订货明细。/*脚本文件名:*/USE NorthwindSQL SELECT a.客户编号,a.公司名称,a.地址,a.连络人,a.电话,b.订单号码,b.订单日期,b.运费,b.收货人,c.产品编号,c.单价,c.数量,c.折扣 FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 WHERE a.地址 LIK

20、E%北京市%AND b.订单日期 BETWEEN 07/01/1996 AND 07/31/1996 2.查询出所有曾经在 1996 年订货的客户公司名称和所订购的产品明细/*脚本文件名:*/USE NorthwindSQL SELECT a.公司名称,b.订单日期,d.*FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c INNER JOIN 产品资料 d ON d.产品编号=c.产品编号 ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 WHERE YEAR(b.订单日期)=1996 3.假如 ERNSH 公司是我们的忠实客户，我们

21、要查询出该公司在 1998 年所下的订单，以及负责处理这些客户订单的员工基本数据：/*脚本文件名:*/USE NorthwindSQL SELECT a.公司名称,b.订单号码,b.订单日期,c.*FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 员工 c ON c.员工编号=b.员工编号 ON a.客户编号=b.客户编号 WHERE a.客户编号=ERNSH AND YEAR(b.订单日期)=1998 8.10.统计运算的高手：聚合函数 聚合函数专用于 SELECT 语句中。COUNT 主要用于计算查询结果中的数据条数，通常用 COUNT(*)的形式。事实上，CO

22、UNT()是唯一允许使用通配符作为参数的聚合函数。1.计算出“飞狐工作室”表中的数据记录条数/*脚本文件名:*/USE NorthwindSQL SELECT COUNT(*)FROM 飞狐工作室 2.计算出“飞狐工作室”表中有多少员工住在北京市/*脚本文件名:*/USE NorthwindSQL SELECT COUNT(*)FROM 飞狐工作室 WHERE 住址 LIKE%北京市%3.计算出“飞狐工作室”表中有年龄介于 3040 之间的员工有多少/*脚本文件名:*/USE NorthwindSQL SELECT COUNT(*)FROM 飞狐工作室 WHERE DATEDIFF(yy,出生

23、日期,GETDATE()BETWEEN 30 AND 40 4.查询出在 1996 年 7 月份下订单的北京市客户有多少位/*脚本文件名:*/USE NorthwindSQL SELECT COUNT(*)AS 客户数目 FROM 客户 a INNER JOIN 订货主档 b ON a.客户编号=b.客户编号 WHERE a.地址 LIKE%北京市%AND b.订单日期 BETWEEN 07/01/1996 AND 07/31/1996 MIN 能够计算出最小值。1.查询出“飞狐工作室”表中，最低的目前薪资是多少。/*脚本文件名:*/USE NorthwindSQL SELECT MIN(目前

24、薪资)AS 最低薪资 FROM 飞狐工作室 注意：利用 MIN 查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。2.查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。/*脚本文件名:*/USE NorthwindSQL SELECT YEAR(GETDATE()-YEAR(MIN(出生日期)AS 最高年龄 FROM 飞狐工作室 SELECT DATEDIFF(yyyy,MIN(出生日期),GETDATE()AS 最高年龄 FROM 飞狐工作室 SELECT MAX(DATEDIFF(yyyy,出生日期,GETDATE()AS 最高年龄 FROM 飞狐

25、工作室 MAX 能够计算出最大值。AVG 能够计算出平均值。1.计算出“飞狐工作室”表中员工的平均年龄/*脚本文件名:*/USE NorthwindSQL SELECT AVG(DATEDIFF(yyyy,出生日期,GETDATE()AS 平均年龄 FROM 飞狐工作室 SUM 求和。1.计算出所有客户的采购总金额/*脚本文件名:*/USE NorthwindSQL SELECT 客户采买总金额=SUM(单价*数量*(1-折扣)FROM 订货明细 聚合函数注意事项：可以将查询结果存储到一个变量中/*脚本文件名:*/USE NorthwindSQL DECLARE AverageSalary m

26、oney -将计算所得的平均薪资储存至变数 AverageSalary 中 SELECT AverageSalary=AVG(目前薪资)FROM 飞狐工作室/*STR(float_expression ,length ,decimal ),返回由数字数据转换来的字符数据。*/PRINT 薪资大于平均薪资+STR(AverageSalary,10,4)+的员工:PRINT REPLICATE(-,35)SELECT 姓名,目前薪资 FROM 飞狐工作室 WHERE 目前薪资 AverageSalary 同一 SELECT 语句中，可以分别使用不同的聚合函数/*脚本文件名:*/USE Northw

27、indSQL SELECT MAX(目前薪资)AS 最高薪资,-看看这种设定列标题的方式 MIN(目前薪资)AS 最低薪资,AVG(目前薪资)AS 平均薪资,SUM(目前薪资)AS 薪资总额 FROM 飞狐工作室 聚合函数中允许包含 DISTINCT 关键字，用于排除重复行。1.计算出 1996 年 8 月份的发票开给了几位客户。/*脚本文件名:*/USE NorthwindSQL SELECT COUNT(DISTINCT 客户编号)AS 客户数目 FROM 订货主档 WHERE 订单日期 BETWEEN 08/01/1996 AND 08/31/1996 任务：统计 teacher 表中职

28、称为讲师的教师人数；查询学生的入学平均分数、最低分、最高分；查询学号为01的学生选课门数；查询学生张劲的选课门数；查询学生张劲选修课程的最高分、最低分、平均分。8.11.数据分组小计 利用 GROUP BY 子句，可以根据一个或多个组的值将查询中的数据记录分组。1.计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。/*脚本文件名:*/USE NorthwindSQL SELECT 部门,COUNT(*)AS 部门员工人数,MAX(目前薪资)AS 部门最高薪资,MIN(目前薪资)AS 部门最低薪资,AVG(目前薪资)AS 部门平均薪资 FROM 飞狐工作室 GRO

29、UP BY 部门 任务：统计 teacher 表中各类职称的教师人数；统计各课程的最高分、最低分、平均分。2.计算出各个客户的采购次数和采购总金额。/*脚本文件名:*/USE NorthwindSQL SELECT a.公司名称,COUNT(DISTINCT b.订单号码)AS 采购次数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额 FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 GROUP BY a.客户编号,a.公司名称 3.查询出每一位业务人员的销售总数量。/*

30、脚本文件名:*/USE NorthwindSQL SELECT a.员工编号,a.姓名,SUM(c.数量)AS 销售总数 FROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.员工编号=b.员工编号 GROUP BY a.员工编号,a.姓名 4.查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。/*脚本文件名:*/EXEC sp_dboption NorthwindSQL,select into/bulkcopy,TRUE -设为 true 能加快 select into 的处

31、理速度 USE NorthwindSQL-首先，生成一张 aMount 表，包含每一位业务人员的销售总数 SELECT a.员工编号,a.姓名,SUM(c.数量)AS 销售总数 INTO aMount FROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.员工编号=b.员工编号 GROUP BY a.员工编号,a.姓名 SELECT a.员工编号,a.姓名,a.销售总数,COUNT(b.员工编号)AS 业绩比此人好之业务员的人数,AVG(b.销售总数)AS 业绩比此人好之业务员的平均销售总数 FROM aMoun

32、t a inner join aMount b ON a.销售总数 30000-不可以用 HAVING 部门的平均薪资 30000 任务：查询选修了选修 4 门以上（包括 4 门）选修课的学生；查询订单数查过 10 张的客户。2.查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名 AS 重复的姓名,COUNT(*)AS 重复的人数 FROM 飞狐工作室 GROUP BY 姓名 Having COUNT(*)1 3.计算出订单超过 10 张的各家客户的订单数目，及其采购总金额。/*脚

33、本文件名:*/USE NorthwindSQL SELECT a.公司名称,COUNT(DISTINCT b.订单号码)AS 订单张数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额 FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 GROUP BY a.公司名称 HAVING COUNT(DISTINCT b.订单号码)10 8.13.ORDER BY 子句的使用 利用 ORDER BY 子句，可以根据一个或多个字段的数据排序查询出的结果。1.按照薪资所得的高低顺序，列

34、出“飞狐工作室”表中所有员工的姓名、性别和薪资。/*脚本文件名:*/USE NorthwindSQL SELECT 姓名,性别=CASE 性别 WHEN 0 THEN 女 WHEN 1 THEN 男 END,目前薪资 FROM 飞狐工作室 ORDER BY 目前薪资 DESC 司名称,COUNT(DISTINCT b.订单号码)AS 采购次数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额 FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 GROUP BY a.公司名称

35、ORDER BY 采购次数 DESC,采购总金额 8.14.查询名列前茅或落后者 先利用 ORDER BY 排序，然后利用关键字 TOP n PERCENT来取出从头算起的前几名或从头算起的多少百分比。1.查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。/*脚本文件名:*/USE NorthwindSQL SELECT TOP 1 姓名 AS 薪资最低之员工姓名,目前薪资 FROM 飞狐工作室 ORDER BY 目前薪资 2.查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。3.查询出“飞狐工作室”中，薪资最高的前 10 名员工的姓名及其薪资。4.查询出“飞狐工作室”中，薪资倒数前 10

36、 名员工的姓名及其薪资。5.查询出“飞狐工作室”中，部门的平均薪资最高的前 3 个部门。/*脚本文件名:*/USE NorthwindSQL SELECT TOP 3 部门 AS 平均薪资最高的前三个部门,AVG(目前薪资)AS 平均薪资 FROM 飞狐工作室 GROUP BY 部门 ORDER BY 2 DESC 6.查询出“飞狐工作室”中，目前薪资最高的前 10的员工。USE NorthwindSQL SELECT TOP 10 PERCENT 姓名 ,目前薪资 FROM 飞狐工作室 ORDER BY 目前薪资 任务：查询入学成绩排名前 10 的学生。7.查询出销售业绩最佳的前 3 名业务

37、员。/*脚本文件名:*/USE NorthwindSQL SELECT TOP 3 a.员工编号,a.姓名 AS 业务员姓名,SUM(c.单价*c.数量*(1-c.折扣)AS 销售总金额 FROM 员工 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.员工编号=b.员工编号 GROUP BY a.员工编号,a.姓名 ORDER BY 3 DESC 8.查询出在 1996 年采购总金额最高的客户（并显示客户的采购次数）。/*脚本文件名:*/USE NorthwindSQL SELECT TOP 1 a.公司名称,COUNT(

38、DISTINCT b.订单号码)AS 采购次数,SUM(c.单价*c.数量*(1-c.折扣)AS 采购总金额 FROM 客户 a INNER JOIN 订货主档 b INNER JOIN 订货明细 c ON b.订单号码=c.订单号码 ON a.客户编号=b.客户编号 WHERE YEAR(b.订单日期)=1996 GROUP BY a.客户编号,a.公司名称 ORDER BY 3 DESC 8.15.活用子查询(SubQuery)（高级内容）子查询：就是包含在某一个 SELECT、INSERT、UPDATE、DELETE 命令中的 SELECT查询。在 SELECT、INSERT、UPDAT

39、E、DELETE 命令中凡是允许是一个表达式的地方均可以包含子查询。子查询甚至可以再包含一个子查询。如：蓝色部分为子查询/*脚本文件名:*/USE NorthwindSQL SELECT 订货主档.订单号码,订货主档.订单日期,(SELECT MAX(订货明细.单价)FROM 订货明细 WHERE 订货主档.订单号码=订货明细.订单号码)AS 最高单价 FROM 订货主档 1.本例以子查询的返回值作为 WHERE 子句的条件值，以便查询哪些员工的薪资和公司中最低薪资人员相同。/*脚本文件名:*/USE NorthwindSQL SELECT a.姓名,a.目前薪资 FROM 飞狐工作室 a W

40、HERE a.目前薪资=(SELECT MIN(b.目前薪资)FROM 飞狐工作室 b)-与以下代码实现功能相同 declare x money select x=min(目前薪资)from 飞狐工作室 select 姓名 from 飞狐工作室 where 目前薪资=x 2.本例通过子查询得知有哪些员工的薪资比公司的平均薪资还高出50/*脚本文件名:*/USE NorthwindSQL SELECT a.姓名,a.目前薪资 FROM 飞狐工作室 a WHERE a.目前薪资 (SELECT AVG(b.目前薪资)*FROM 飞狐工作室 b)或 (SELECT AVG(b.目前薪资)FROM 飞

41、狐工作室 b)*3.本例通过子查询查询出人数比业务部门还多的各部门员工的平均薪资。/*脚本文件名:*/USE NorthwindSQL SELECT 部门,COUNT(*)AS 部门人数,AVG(目前薪资)AS 平均薪资 FROM 飞狐工作室 GROUP BY 部门 HAVING COUNT(*)(SELECT COUNT(*)FROM 飞狐工作室 WHERE 部门=业务部)-与以下代码实现功能相同（先计算出业务部人数，存入一个变量中）DECLARE x int SELECT x=count(*)FROM 飞狐工作室 WHERE 部门=业务部 SELECT 部门,avg(目前薪资),count

42、(*)AS 人数 FROM 飞狐工作室 GROUP BY 部门 HAVING count(*)x 注：子查询必须包含在一对小括号内。“EXISTS”子 查 询 的 测 试 使用 EXISTS 关键字，可检查是否至少有一条数据记录存在于子查询的查询结果中。在子查询的 SELECT 语句选择列表中通常使用通配符星号*。1.查询出哪些公司并没有采购。（要查询采购过的公司资料呢）/*脚本文件名:*/USE NorthwindSQL SELECT 客户.客户编号,客户.公司名称 FROM 客户 WHERE NOT EXISTS (SELECT*FROM 订货主档 WHERE 订货主档.客户编号=客户.客户编号)“IN”子 查 询 的 测 试 1.查询出哪些公司并没有采购。/*脚本文件名:*/USE NorthwindSQL SELECT a.公司名称 FROM 客户 a WHERE a.客户编号 NOT IN (SELECT b.客户编号 FROM 订货主档 b)2.查询出哪些公司曾经采购过。（代码略）附注：其实用 EXISTS 和 IN 可以达到相同的效果。任务：用子查询查询选修课成绩不及格的学生学号和姓名。第8章 结束