第08章 数据的查询、汇总 、统计和分析

8. 数据的查询、汇总、统计和分析

本章主题

l        SELECT语句

l        简单的SELECT查询

l        设置查询结果的字段名

l        关键字ALL和DISTINCT的使用

l        查询结果的输出目的地

l        WHERE子句的条件搜索功能

l        SELECT的通配符

l        字符和通配符冲突时的解决方法

l        连接条件设置

l        统计运算的高手：聚合函数

l        数据分组小计

l        HAVING子句的使用

l        ORDER BY子句的使用

l        查询名列前茅或落后者

l        活用子查询（SubQuery）

l        自连接（Self-Joins）

l        合并查询结果

 

精彩内容不容错过！这一章内容最重要了！！★★★★★

 

本章将深入剖析SELECT命令。

准备工作

准备工作：在示例代码ch8目录中，数据库(NorthwindSQL)

文件名：NorthwindSQL.mdf、NorthwindSQL.ldf

 

8.1. SELECT命令

SELECT是一个用来从一个或多个表中获取数据的SQL命令。

 

8.2. 简单的SELECT查询

如：

USE NorthwindSQL

SELECT 身份证号码，姓名，电话号码  -- ß这里是字段列表

FROM 飞狐工作室

 

SELECT命令至少包含：

• 要出现在查询结果中的字段列表，如：身份证号码，姓名，电话号码
• 字段来自哪些表，如：FROM飞狐工作室
• 字段列表也可以是由字段、常量和函数组成的表达式
• 要列出所有字段，只须用 * 号

8.3. 设置查询结果的字段名

查询需求

请从数据库pubs的authors表，查询出所有作者的代号、姓名、电话号码及住址，而且请使用中文文字作为查询结果的各字段名。

解答

/*脚本文件名: Demo91.sql*/

USEpubs

 

SELECT作者代号 = au_id,

       姓名 = au_fname+au_lname,

       电话号码 = phone,

       住址 = address

  FROM authors

 

或 （看看你更喜欢哪一种格式？）

/*脚本文件名: Demo92.sql*/

USEpubs

 

SELECTau_id  AS 作者代号,

       au_fname+au_lname  AS 姓名,

       phone AS 电话号码,

       address AS 住址

  FROM authors

 

注意：

• 如果您设置的的字段名包含空格，则须加上单引号

SELECT  au_fname+au_lname  AS  ‘Name of Author’

FROM  authors

 

8.4. 关键字ALL和DISTINCT的使用

1. 查询需求

请列出“飞狐工作室”表中所有员工的雇用日期，但是日期相同者只列出一次即可。

USE  NorthwindSQL

SELECT DISTINCT 雇用日期FROM 飞狐工作室

 

1. 查询需求

请列出“飞狐工作室”表中有哪些部门。

USE  NorthwindSQL

SELECT DISTINCT 部门 FROM飞狐工作室

――一个问题，下面语句代表什么意思？？？

SELECT DISTINCT 姓名，部门FROM飞狐工作室

 

8.5. 查询结果的输出目的地

为什么要讨论查询结果的输出目的地？

答：可能希望将查询结果输出到某个存储处以便进行进一步的处理。

 

举例说明INTO子句的用法：

• 将查询结果存储到当前数据库中的新表MyTmpTable中

USE NorthwindSQL

SELECT  *  INTO MyTmpTable  FROM 飞狐工作室

注：INTO子句会生成相应的表，如果表已存在则提示错误“表已存在”。

8.6. WHERE子句的条件搜索功能

1. 查询需求

请列出“飞狐工作室”表中目前薪资大于60000的员工。

/*脚本文件名: Demo93.sql*/

USE NorthwindSQL

SELECT 姓名,目前薪资FROM 飞狐工作室

  WHERE 目前薪资 > 60000

 

1. 查询需求

请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门任职的员工姓名。

/*脚本文件名: Demo94.sql*/

USE NorthwindSQL

SELECT 姓名,部门FROM 飞狐工作室

  WHERE 部门 IN ('资讯部','行销部','业务部')

 

1. 查询需求

请列出“飞狐工作室”表在信息部、行销部和业务部等3个部门以外任职的员工姓名。

/*脚本文件名: Demo95.sql*/

USE NorthwindSQL

SELECT 姓名,部门FROM 飞狐工作室

  WHERE 部门NOT  IN ('资讯部','行销部','业务部')

 

1. 查询需求

请列出“飞狐工作室”表中，本月出生的员工姓名和出生日期。

/*脚本文件名: Demo910.sql*/

USENorthwindSQL

SELECT姓名,出生日期 FROM 飞狐工作室

  WHEREMONTH(出生日期) = MONTH(GETDATE())

 

1. 查询需求

请列出“飞狐工作室”表中，年龄大于20岁的每一位员工的姓名。

/*脚本文件名: Demo911.sql*/

USENorthwindSQL

SELECT姓名

  FROM 飞狐工作室

  WHERE DATEDIFF(year,出生日期,GETDATE())>20

 

8.7. SELECT的通配符

共有5个通配符。

*（星号）

• 用于字段列表，代表源表中的所有的字段

 

/*脚本文件名: Demo913.sql*/

USENorthwindSQL

 

--连接两张表“客户”表和“订货主档”表

SELECT*

  FROM 客户 INNER JOIN 订货主档

       ON客户.客户编号 = 订货主档.客户编号

  WHERE 订货主档.订单日期 BETWEEN '08/01/1996'AND '08/31/1996'

 

/*脚本文件名: Demo914.sql*/

USENorthwindSQL

 

SELECT客户.公司名称,订货主档.*

  FROM 客户 INNER JOIN 订货主档

       ON客户.客户编号 = 订货主档.客户编号

  WHERE 订货主档.订单日期 BETWEEN '08/01/1996'AND '08/31/1996'

 

%（百分号）

• 只能用在WHERE子句中，代表0个或0个以上的字符。

如：ABC%代表ABC开头的字符串。

• 百分号通常与运算符LIKE搭配使用。

/*脚本文件名: Demo915.sql*/

USENorthwindSQL

 

SELECT姓名 FROM 飞狐工作室

  WHERE 姓名 LIKE '%光%' --表示查询姓名中包含“光”字的记录

 

_（下划线）

• 只能用在WHERE子句中，代表1个字符。

如：_A%代表第二个字符为A的字符串。

• 下划线通常与运算符LIKE搭配使用。

 

［］（中括号）

• 只能用在WHERE子句中，用来限定任何一个单个字符介于指定的范围或集合中。
• 通常与运算符LIKE搭配使用。

/*脚本文件名: Demo923.sql*/

USEpubs

 

SELECTau_fname,au_lname FROM authors

  WHERE au_lname LIKE'[P-Z]inger' 

--表示第一个字符为P~Z之间且后五个字符为inger

 

/*脚本文件名: Demo924.sql*/

USENorthwindSQL

 

SELECT姓名 FROM 飞狐工作室

  WHERE 姓名LIKE '[ACD张李]%'  --表示什么？

  ORDER BY 姓名

 

［^］（中括号中包含^号）

• 只能用在WHERE子句中，用来限定任何一个单个字符不介于指定的范围或集合中。
• 通常与运算符LIKE搭配使用。

/*脚本文件名: Demo927.sql*/

USEpubs

 

SELECTau_fname,au_lname FROM authors

  WHERE au_fnameLIKE '[^H-K]ichel' 

--首字母不介于H~K之间且后为ichel

 

/*脚本文件名: Demo930.sql*/

USENorthwindSQL

 

SELECT身份证字号,姓名 FROM 飞狐工作室

  WHERE 身份证字号LIKE '[^ALM]%'   --这个表示什么意思？

 

8.8. 字符和通配符冲突时的解决方法

可以使用ESCAPE子句来SQLServer哪一个字符是常量字符而并非通配符。

用专业术语来说，ESCAPE子句所指定的字符称为“转义符”。

/*脚本文件名: Demo920.sql*/

USENorthwindSQL

 

SELECT姓名,住址 FROM 飞狐工作室

  WHERE 住址LIKE '%\_%' ESCAPE '\' 

--通知符号\后的字符并非通配符，即查询住址中带下划线的记录

 

8.9. 连接条件设置

本节内容太重要了！

看如下代码，如果TableA有M条记录，TableB有N条记录，则查询结果共有M×N条记录：

SELECT * FROMTableA，TableB

或

SELECT * FROM　TableA  CROSS JOIN  TableB

 

为了避免出现上述情况，有4种连接类型可以选择：

• INNER JOIN

特点：查询结果仅包含连接表中彼此相对应的数据记录。

 

/*脚本文件名:Demo933.sql  本例用于查看每一位客户的订货情况

但是，并未下订单的客户不会出现在查询结果中！

*/

USENorthwindSQL

 

SELECT 客户.客户编号, 客户.公司名称, 客户.连络人, 客户.电话,

       订货主档.订单号码, 订货主档.订单日期, 订货主档.要货日期,

       订货主档.送货日期, 订货主档.送货方式, 订货主档.运费,

       订货主档.收货人,订货主档.送货地址

  FROM 客户 INNER JOIN 订货主档

    ON客户.客户编号 = 订货主档.客户编号

 

更复杂的连接3个表的例子：

/*脚本文件名: Demo934.sql*/

USE NorthwindSQL

 

SELECT 客户.客户编号,客户.公司名称,订货主档.订单号码,

       订货主档.订单日期,订货明细.产品编号,订货明细.单价,

       订货明细.数量, 订货明细.折扣

  FROM 客户 INNER JOIN 订货主档

           INNERJOIN 订货明细

           ON订货主档.订单号码 = 订货明细.订单号码

           ON客户.客户编号 = 订货主档.客户编号

 

• LEFT  OUTER  JOIN（左外连接）

特点：查询结果将包含位于关键字LEFTOUTER JOIN左侧源表中的所有数据记录，但是仅包含右侧源表中相应的数据记录。

 

/*脚本文件名:Demo935.sql  本例用于查看每一位客户的订货情况

但是希望并未下订单的客户也出现在查询结果中！

*/

USE NorthwindSQL

 

SELECT 客户.客户编号,

       客户.公司名称,

       客户.连络人,

       订货主档.订单号码,

       订货主档.收货人

  FROM 客户 LEFT OUTERJOIN 订货主档

    ON客户.客户编号 = 订货主档.客户编号

（加上条件限制试试  WHERE b.订单号码 is NULL）

 

• RIGHT  OUTER  JOIN（右外连接）

特点：查询结果将包含位于关键字RIGHTOUTER JOIN右侧源表中的所有数据记录，但是仅包含左侧源表中相应的数据记录。

 

/*脚本文件名:Demo936.sql  查看每一种产品的销售情况

但是希望那些没有人订购的产品数据也在查询结果中出现

*/

USE NorthwindSQL

 

SELECT 订货明细.订单号码,

       订货明细.单价,

       订货明细.数量,

       订货明细.折扣,

       产品资料.产品编号,

       产品资料.产品

  FROM 订货明细 RIGHTOUTER JOIN 产品资料

       ON订货明细.产品编号 = 产品资料.产品编号

（加上条件限制试试   WHERE 订货明细.订单号码is NULL）

 

• FULL  OUTER  JOIN（全外连接）

特点：查询结果将包含位于关键字FULLOUTER JOIN左右两侧源表中的所有数据记录。

 

/*脚本文件名:Demo938.sql   给表指定别名*/

USE NorthwindSQL

SELECT a.客户编号,

       a.公司名称,

       a.连络人,

       a.电话,

       b.订单号码,

       b.订单日期,

       b.要货日期,

       b.送货日期,

       b.送货方式,

       b.运费,

       b.收货人,

       b.送货地址

  FROM 客户 a  INNERJOIN 订货主档b

    ON  a.客户编号 = b.客户编号

 

小技巧：

给表指定一个较短的别名，最大的好处是缩减了SELECT语句的长度。

 

范例：

1. 查询出在1996年7月份采购的北京市客户的基本数据、订单数据、订货明细。

/*脚本文件名: Demo939.sql*/

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.地址LIKE  '%北京市%'  AND

          b.订单日期BETWEEN '07/01/1996'  AND  '07/31/1996'

 

1. 查询出所有曾经在1996年订货的客户公司名称和所订购的产品明细

/*脚本文件名: Demo940.sql*/

USE NorthwindSQL

 

SELECT a.公司名称,b.订单日期,d.*

  FROM 客户 a   INNER JOIN 订货主档 b

                 INNERJOIN 订货明细 c

                 INNERJOIN 产品资料 d

                 ON  d.产品编号 = c.产品编号

                 ON  b.订单号码 = c.订单号码

                 ON  a.客户编号 = b.客户编号

  WHERE  YEAR(b.订单日期) = 1996

 

1. 假如ERNSH公司是我们的忠实客户，我们要查询出该公司在1998年所下的订单，以及负责处理这些客户订单的员工基本数据：

/*脚本文件名: Demo941.sql*/

USE NorthwindSQL

 

SELECT a.公司名称,b.订单号码,b.订单日期,c.*

  FROM 客户 a  INNERJOIN 订货主档 b

                 INNERJOIN 员工 c

                 ON  c.员工编号 = b.员工编号

                 ON  a.客户编号 = b.客户编号

  WHERE  a.客户编号 = 'ERNSH'  AND

        YEAR(b.订单日期) = 1998

 

8.10. 统计运算的高手：聚合函数

聚合函数专用于SELECT语句中。

• COUNT

n       主要用于计算查询结果中的数据条数，通常用COUNT(*)的形式。

n       事实上，COUNT()是唯一允许使用通配符作为参数的聚合函数。

 

1. 计算出“飞狐工作室”表中的数据记录条数

/*脚本文件名: Demo942.sql*/

USE NorthwindSQL

 

SELECT  COUNT(*)  FROM 飞狐工作室

 

1. 计算出“飞狐工作室”表中有多少员工住在北京市

/*脚本文件名: Demo943.sql*/

USE NorthwindSQL

 

SELECT  COUNT(*)  FROM 飞狐工作室

  WHERE 住址 LIKE '%北京市%'

 

1. 计算出“飞狐工作室”表中有年龄介于30～40之间的员工有多少

/*脚本文件名: Demo944.sql*/

USE NorthwindSQL

 

SELECT  COUNT(*)  FROM 飞狐工作室

  WHERE  DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 30 AND 40

 

1. 查询出在1996年7月份下订单的北京市客户有多少位

/*脚本文件名: Demo945.sql*/

USE NorthwindSQL

 

SELECT COUNT(*)  AS 客户数目

  FROM 客户 a INNER JOIN 订货主档b

        ONa.客户编号 = b.客户编号

  WHERE  a.地址LIKE  '%北京市%'  AND

        b.订单日期BETWEEN '07/01/1996'AND '07/31/1996'

 

• MIN

n       能够计算出最小值。

1. 查询出“飞狐工作室”表中，最低的目前薪资是多少。

/*脚本文件名: Demo946.sql*/

USE NorthwindSQL

 

SELECT  MIN(目前薪资) AS 最低薪资

  FROM 飞狐工作室

 

注意：

利用MIN查询出某一字段的最小值时，并无法同时得知该字段为最小值的数据记录的其他字段内容。

 

1. 查询出“飞狐工作室”表中，年龄最大者是几岁（提供了三种方法）。

/*脚本文件名: Demo947.sql*/

USE NorthwindSQL

 

SELECT YEAR(GETDATE())- YEAR(MIN(出生日期)) AS最高年龄

  FROM 飞狐工作室

 

SELECT DATEDIFF(yyyy,MIN(出生日期),GETDATE()) AS 最高年龄

  FROM 飞狐工作室

 

SELECT MAX(DATEDIFF(yyyy,出生日期,GETDATE())) AS 最高年龄

  FROM 飞狐工作室

 

• MAX

n       能够计算出最大值。

• AVG

n       能够计算出平均值。

1. 计算出“飞狐工作室”表中员工的平均年龄

/*脚本文件名: Demo951.sql*/

USE NorthwindSQL

 

SELECT  AVG(DATEDIFF(yyyy,出生日期,GETDATE()))  AS 平均年龄

  FROM  飞狐工作室

 

• SUM

n       求和。

1. 计算出所有客户的采购总金额

/*脚本文件名: Demo952.sql*/

USE NorthwindSQL

 

SELECT 客户采买总金额 =

          SUM(订货明细.单价*订货明细.数量*(1-订货明细.折扣))

  FROM 订货明细

 

聚合函数注意事项：

• 可以将查询结果存储到一个变量中

/*脚本文件名: Demo953.sql*/

USE NorthwindSQL

 

DECLARE@AverageSalary money

 

-- 将计算所得的平均薪资储存至变数 @AverageSalary 中

--看下面，这就是我为什么不喜欢使用＝号来设定列标题的原因！

SELECT @AverageSalary= AVG(目前薪资)

  FROM 飞狐工作室

 

PRINT '薪资大于平均薪资 '+STR(@AverageSalary,10,4)+'的员工:'

PRINT REPLICATE('-',35)

 

SELECT 姓名,目前薪资FROM 飞狐工作室

  WHERE 目前薪资 > @AverageSalary

 

• 同一SELECT语句中，可以分别使用不同的聚合函数

/*脚本文件名: Demo954.sql*/

USE NorthwindSQL

 

SELECT MAX(目前薪资)AS 最高薪资,  --看看这种设定列标题的方式

        MIN(目前薪资) AS 最低薪资,

        AVG(目前薪资) AS 平均薪资,

        SUM(目前薪资) AS 薪资总额

  FROM 飞狐工作室

 

• 聚合函数中允许包含DISTINCT关键字，用于排除重复行。

1. 计算出1996年8月份的发票开给了几位客户。

/*脚本文件名: Demo955.sql*/

USE NorthwindSQL

 

SELECT COUNT(DISTINCT 客户编号) AS客户数目

  FROM 订货主档

  WHERE 订单日期 BETWEEN '08/01/1996' AND '08/31/1996'

 

8.11. 数据分组小计

利用GROUP BY子句，可以根据一个或多个组的值将查询中的数据记录分组。

1. 计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。

/*脚本文件名: Demo956.sql*/

USE NorthwindSQL

 

SELECT 部门,

       COUNT(*)AS 部门员工人数,

       MAX(目前薪资) AS部门最高薪资,

       MIN(目前薪资) AS部门最低薪资,

       AVG(目前薪资) AS部门平均薪资

  FROM 飞狐工作室

  GROUP BY 部门

 

1. 计算出每一位客户的采购次数和采购总金额。

/*脚本文件名: Demo957.sql*/

USE NorthwindSQL

 

SELECT a.公司名称,

       COUNT(DISTINCT b.订单号码) AS 采购次数,

       SUM(c.单价 * c.数量 * (1 - c.折扣))AS 采购总金额

  FROM 客户 a

       INNER JOIN订货主档 b

       INNERJOIN 订货明细 c

       ONb.订单号码 = c.订单号码

       ONa.客户编号 = b.客户编号

  GROUP BY a.公司名称

 

1. 查询出每一位业务人员的销售总数。

/*脚本文件名: Demo958.sql*/

USE NorthwindSQL

 

SELECT a.员工编号,a.姓名,

       SUM(c.数量) AS 销售总数

  FROM 员工 a

       INNERJOIN 订货主档 b

       INNERJOIN 订货明细 c

       ONb.订单号码 = c.订单号码

       ONa.员工编号 = b.员工编号

  GROUP BY a.员工编号,a.姓名

 

1. 查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。

/*脚本文件名: Demo959.sql*/

EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','TRUE' 

--设为true能加快select into的处理速度

 

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 业绩比此人好之业务员的平均销售总数

  FROMaMount a, aMount b

  WHEREa.销售总数 < b.销售总数

  GROUP BY a.员工编号,a.姓名,a.销售总数

 

EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','FALSE'

DROP TABLE aMount

 

1. 为“飞狐工作室”表的各部门计算出“女性未婚”、“女性已婚”、“男性未婚”、“男性已婚”员工的最低薪资、最高薪资和平均薪资。

/*脚本文件名: Demo960.sql*/

USE NorthwindSQL

--在这里要求显示男、女、已婚、未婚，而不是0、1

SELECT 部门,

       性别 = CASE 员工性别

               WHEN0 THEN '女'

               WHEN1 THEN '男'

             END,

       婚姻状况 = CASE 婚姻状况

                   WHEN0 THEN '未婚'

                   WHEN1 THEN '已婚'

                 END,         

       最低薪资 = MIN(目前薪资),

       最高薪资 = MAX(目前薪资),

       平均薪资 = AVG(目前薪资)

  FROM 飞狐工作室

  GROUP BY 部门,员工性别, 婚姻状况

 

--或者采用下面的命令

SELECT 部门,

       员工性别,

       婚姻状况,         

       最低薪资 = MIN(目前薪资),

       最高薪资 = MAX(目前薪资),

       平均薪资 = AVG(目前薪资)

  FROM 飞狐工作室

  GROUP BY 部门,员工性别,婚姻状况

 

8.12. HAVING 子句的使用

HAVING搜索条件子句与GROUP BY子句合用，用来设置查询结果中“组”所需符合的条件。即对要列出哪几个分组进行了限定。

 

1. 计算出 “飞狐工作室”表中，部门平均薪资大于30000的各个部门的薪资最高值、薪资最小值、薪资平均值及人数。

/*脚本文件名: Demo961.sql*/

USENorthwindSQL

 

SELECT 部门,

       AVG(目前薪资) AS 部门的平均薪资,

       COUNT(*) AS 各部门之人数,

       MAX(目前薪资) AS 部门中的最高薪,

       MIN(目前薪资) AS 部门中的最低薪

  FROM 飞狐工作室

  GROUP BY 部门

  HAVINGAVG(目前薪资) > 30000

 

1. 查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。

/*脚本文件名: Demo962.sql*/

USENorthwindSQL

 

SELECT 姓名 AS 重复的姓名,

       COUNT(*) AS 重复的人数

  FROM 飞狐工作室

  GROUP BY 姓名

  Having COUNT(*) > 1

 

1. 计算出订单超过10张的各家客户的订单数目，及其采购总金额。

/*脚本文件名: Demo963.sql*/

USENorthwindSQL

 

SELECT a.公司名称,

       COUNT(DISTINCT b.订单号码) AS 订单张数,

       SUM(c.单价 * c.数量 * (1 - c.折扣)) AS 采购总金额

  FROM 客户 a

       INNERJOIN 订货主档 b

       INNERJOIN 订货明细 c

       ONb.订单号码 = c.订单号码

       ONa.客户编号 = b.客户编号

  GROUP BY a.公司名称

  HAVINGCOUNT(DISTINCT b.订单号码) > 10

 

8.13. ORDER BY 子句的使用

利用ORDERBY子句，可以根据一个或多个字段的数据排序查询出的结果。

1. 按照薪资所得的高低顺序，列出“飞狐工作室”表中所有员工的姓名、性别和薪资。

/*脚本文件名: Demo964.sql*/

USENorthwindSQL

 

SELECT 姓名,

       性别 = CASE 性别

               WHEN 0 THEN '女'

               WHEN 1 THEN '男'

             END,

       目前薪资

  FROM 飞狐工作室

  ORDER BY 目前薪资 DESC  //可用ORDER BY 3 DESC

 

1. 将“飞狐工作室”表中各部门的平均薪资由低到高列出来。

/*脚本文件名: Demo965.sql*/

USENorthwindSQL

 

SELECT 部门,

       AVG(目前薪资) AS 平均薪资

  FROM 飞狐工作室

  GROUP BY 部门

  ORDER BY 2

 

1. 列出“飞狐工作室”表中所有员工的隶属部门、员工姓名、性别和薪资，按部门、性别（降序）、目前薪资（升序）来排列。

/*脚本文件名: Demo966.sql*/

USENorthwindSQL

 

SELECT 部门,

       姓名,

       性别 = CASE 性别

               WHEN 0 THEN '女'

               WHEN 1 THEN '男'

             END,

       目前薪资

  FROM 飞狐工作室

  ORDER BY 部门,性别 DESC,目前薪资 ASC

 

1. 计算出每一位客户的采购次数和采购总金额。先按照采购次数由多到少排列，而采购次数相同者，则再按照采购总金额由低到高排列。

/*脚本文件名: Demo967.sql*/

USENorthwindSQL

 

SELECT a.公司名称,

       COUNT(DISTINCTb.订单号码) AS 采购次数,

       SUM(c.单价 * c.数量 * (1 - c.折扣)) AS 采购总金额

  FROM 客户 a

       INNER JOIN 订货主档 b

       INNER JOIN 订货明细 c

       ON b.订单号码 = c.订单号码

       ON a.客户编号 = b.客户编号

  GROUP BY a.公司名称

  ORDER BY采购次数DESC, 采购总金额

 

8.14. 查询名列前茅或落后者

先利用ORDER BY排序，然后利用关键字TOPn [PERCENT]来取出从头算起的前几名或从头算起的多少百分比。

1. 查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。

/*脚本文件名: Demo968.sql*/

USENorthwindSQL

 

SELECT TOP 1

       姓名 AS 薪资最低之员工姓名,

       目前薪资

  FROM 飞狐工作室

  ORDER BY 目前薪资

 

1. 查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。
2. 查询出“飞狐工作室”中，薪资最高的前10名员工的姓名及其薪资。
3. 查询出“飞狐工作室”中，薪资倒数前10名员工的姓名及其薪资。
4. 查询出“飞狐工作室”中，部门的平均薪资最高的前3个部门。

/*脚本文件名: Demo972.sql*/

USENorthwindSQL

 

SELECT TOP 3

       部门 AS 平均薪资最高的前三个部门,

       AVG(目前薪资) AS 平均薪资

  FROM 飞狐工作室

  GROUP BY 部门

  ORDER BY 2 DESC

1. 查询出“飞狐工作室”中，目前薪资最高的前10％的员工。
2. 查询出销售业绩最佳的前3名业务员。

/*脚本文件名: Demo974.sql*/

USENorthwindSQL

 

SELECT TOP 3

       a.员工编号,

       a.姓名 AS 业务员姓名,

       SUM(c.单价 * c.数量*(1 - c.折扣)) AS 销售总金额

  FROM 员工 a

       INNER JOIN 订货主档 b

       INNER JOIN 订货明细 c

       ON b.订单号码 = c.订单号码

       ONa.员工编号 = b.员工编号

  GROUP BY a.员工编号,a.姓名

  ORDER BY 3 DESC

1. 查询出在1996年采购总金额最高的客户。

/*脚本文件名: Demo975.sql*/

USENorthwindSQL

 

SELECT TOP 1

       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.客户编号

  WHERE YEAR(b.订单日期) = 1996

  GROUP BY a.公司名称

  ORDER BY 3 DESC

 

8.15. 活用子查询(SubQuery)

n     子查询：就是包含在某一个SELECT、INSERT、UPDATE、DELETE命令中的SELECT查询。

n     在SELECT、INSERT、UPDATE、DELETE命令中凡是允许是一个表达式的地方均可以包含子查询。

n     子查询甚至可以再包含一个子查询。

如：蓝色部分为子查询

/*脚本文件名: Demo976.sql*/

USENorthwindSQL

 

SELECT订货主档.订单号码,订货主档.订单日期,

   (SELECT MAX(订货明细.单价)

     FROM 订货明细

     WHERE 订货主档.订单号码 = 订货明细.订单号码) AS 最高单价

  FROM 订货主档

 

1. 本例以子查询的返回值作为WHERE子句的条件值，以便查询哪些员工的薪资和公司中最低薪资人员相同。

/*脚本文件名: Demo977.sql*/

USENorthwindSQL

SELECT a.姓名,

       a.目前薪资

  FROM 飞狐工作室 a

  WHERE a.目前薪资 =

       (SELECTMIN(b.目前薪资) FROM 飞狐工作室 b)

--与以下代码实现功能相同

declare @x money

select @x=min(目前薪资) from飞狐工作室

select 姓名

  from 飞狐工作室

  where 目前薪资=@x

 

1. 本例通过子查询得知有哪些员工的薪资比公司的平均薪资还高出50％

/*脚本文件名: Demo978.sql*/

USENorthwindSQL

SELECT a.姓名,

       a.目前薪资

  FROM 飞狐工作室 a

  WHERE a.目前薪资 >

       (SELECT AVG(b.目前薪资)* 1.50 FROM 飞狐工作室 b)

 

1. 本例通过子查询查询出人数比业务部门还多的各部门员工的平均薪资。

/*脚本文件名: Demo982.sql*/

USENorthwindSQL

 

SELECT 部门,COUNT(*) AS 部门人数,AVG(目前薪资) AS 平均薪资

  FROM 飞狐工作室

  GROUP BY 部门

  HAVINGCOUNT(*) >

   (SELECT COUNT(*)FROM 飞狐工作室 WHERE 部门 = '业务部')

 

注：

n     子查询必须包含在一对小括号内。

 

“EXISTS”子查询的测试

n     使用EXISTS关键字，可检查是否至少有一条数据记录存在于子查询的查询结果中。

n     在子查询的SELECT语句选择列表中通常使用通配符星号*。

 

1. 查询出哪些公司并没有采购。（要查询采购过的公司资料呢？）

/*脚本文件名: Demo980.sql*/

USENorthwindSQL

 

SELECT 客户.客户编号,客户.公司名称

  FROM 客户

  WHERE NOT EXISTS

        (SELECT*

        FROM 订货主档

        WHERE 订货主档.客户编号 = 客户.客户编号)

 

“IN”子查询的测试

1. 查询出哪些公司并没有采购。

/*脚本文件名: Demo983.sql*/

USENorthwindSQL

 

SELECT a.公司名称

  FROM 客户 a

  WHEREa.客户编号 NOT IN

                (SELECT b.客户编号FROM 订货主档 b)

 

1. 查询出哪些公司曾经采购过。（代码略）

 

附注：其实用EXISTS和IN可以达到相同的效果。

 

第8章　结束