本文介绍如何使用SQL的GROUP BY和ORDER BY子句对数据进行分组和排序，包括计算不同部门的薪资统计、客户的采购情况等，并展示了如何结合HAVING子句筛选特定分组。
8.11. 数据分组小计
利用GROUP BY子句，可以根据一个或多个组的值将查询中的数据记录分组。
1. 计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/* 档案名称: Demo956.sql */
USE NorthwindSQL
SELECT 部门,
COUNT(*) AS 部门员工人数,
MAX(目前薪资) AS 部门最高薪资,
MIN(目前薪资) AS 部门最低薪资,
AVG(目前薪资) AS 部门平均薪资
FROM 飞狐工作室
GROUP BY 部门
2. 计算出每一位客户的采购次数和采购总金额。
/* 档案名称: Demo957.sql */
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.公司名称
3. 查询出每一位业务人员的销售总数。
/* 档案名称: Demo958.sql */
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. 查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。
/* 档案名称: 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 业绩比此人好之业务员的平均销售总数
FROM aMount a, aMount b
WHERE a.销售总数 < b.销售总数
GROUP BY a.员工编号,a.姓名,a.销售总数
EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','FALSE'
DROP TABLE aMount
5. 为“飞狐工作室”表的各部门计算出“女性未婚”、“女性已婚”、“男性未婚”、“男性已婚”员工的最低薪资、最高薪资和平均薪资。
/* 档案名称: Demo960.sql */
USE NorthwindSQL
--本例中，性别、婚姻状况是bit数据类型，SQL Server不允许使用bit类型的字
--段作为分组，这里变通了一下。
SELECT 部门,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
婚姻状况 = CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END,
最低薪资 = MIN(目前薪资),
最高薪资 = MAX(目前薪资),
平均薪资 = AVG(目前薪资)
FROM 飞狐工作室
GROUP BY 部门,
CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END
8.12. HAVING 子句的使用
HAVING搜索条件子句通常与GROUP BY子句合用，用来设置查询结果中“组”所需符合的条件。在这里我们自然会想为什么不用where因为where 不可以用聚合函数
即对要列出哪几个分组进行了限定。
1. 计算出员工“飞狐工作室”表中，部门平均薪资大于30000的各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/* 档案名称: Demo961.sql */
USE NorthwindSQL
SELECT部门,
AVG(目前薪资) AS 部门的平均薪资,
COUNT(*) AS 各部门之人数,
MAX(目前薪资) AS 部门中的最高薪,
MIN(目前薪资) AS 部门中的最低薪
FROM 飞狐工作室
GROUP BY 部门
HAVING AVG(目前薪资) > 30000
2. 查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。
/* 档案名称: Demo962.sql */
USE NorthwindSQL
SELECT 姓名 AS 重复的姓名,
COUNT(*) AS 重复的人数
FROM 飞狐工作室
GROUP BY 姓名
Having COUNT(*) > 1
3. 计算出订单超过10张的各家客户的订单数目，及其采购总金额。
/* 档案名称: Demo963.sql */
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. 按照薪资所得的高低顺序，列出“飞狐工作室”表中所有员工的姓名、性别和薪资。
/* 档案名称: Demo964.sql */
USE NorthwindSQL
SELECT 姓名,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资 DESC //可用ORDER BY 3 DESC
2. 将“飞狐工作室”表中各部门的平均薪资由低到高列出来。
/* 档案名称: Demo965.sql */
USE NorthwindSQL
SELECT 部门,
AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
ORDER BY 2
3. 列出“飞狐工作室”表中所有员工的隶属部门、员工姓名、性别和薪资，按部门、性别（降序）、目前薪资（降序）来排列。
/* 档案名称: Demo966.sql */
USE NorthwindSQL
SELECT 部门,
姓名,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
目前薪资
FROM 飞狐工作室
ORDER BY 部门,性别 DESC,目前薪资 DESC
4. 计算出每一位客户的采购次数和采购总金额。先按照采购次数由多到少排列，而采购次数相同者，则再按照采购总金额由低到高排列。
/* 档案名称: Demo967.sql */
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.公司名称
ORDER BY 2 DESC, 3
8.14. 查询名列前茅或落后者
先利用ORDER BY排序，然后利用关键字TOP n [PERCENT]来取出从头算起的前几名或从头算起的多少百分比。
1. 查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。
/* 档案名称: Demo968.sql */
USE NorthwindSQL
SELECT TOP 1
姓名 AS 薪资最低之员工姓名,
目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资
2. 查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。
3. 查询出“飞狐工作室”中，薪资最高的前10名员工的姓名及其薪资。
4. 查询出“飞狐工作室”中，薪资倒数前10名员工的姓名及其薪资。
5. 查询出“飞狐工作室”中，部门的平均薪资最高的前3个部门。
/* 档案名称: Demo972.sql */
USE NorthwindSQL
SELECT TOP 3
部门 AS 平均薪资最高的前三个部门,
AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
ORDER BY 2 DESC
6. 查询出“飞狐工作室”中，目前薪资最高的前10％的员工。
7. 查询出销售业绩最佳的前3名业务员。
/* 档案名称: Demo974.sql */
USE NorthwindSQL
SELECTTOP 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年采购总金额最高的客户。
/* 档案名称: Demo975.sql */
USE NorthwindSQL
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 */
USE NorthwindSQL
SELECT 订货主档.订单号码,订货主档.订单日期,
(SELECT MAX(订货明细.单价)
FROM 订货明细
WHERE 订货主档.订单号码 = 订货明细.订单号码) AS 最高单价
FROM 订货主档
1. 本例以子查询的返回值作为WHERE子句的条件值，以便查询哪些员工的薪资和公司中最低薪资人员相同。
/* 档案名称: Demo977.sql */
USE NorthwindSQL
SELECT a.姓名,
a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 =
(SELECT MIN(b.目前薪资) FROM 飞狐工作室 b)
2. 本例通过子查询得知有哪些员工的薪资比公司的平均薪资还高出50％
/* 档案名称: Demo978.sql */
USE NorthwindSQL
SELECT a.姓名,
a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 >
(SELECT AVG(b.目前薪资) * 1.50 FROM 飞狐工作室 b)
ORDER BY a.目前薪资
3. 本例通过子查询查询出人数比业务部门还多的各部门员工的平均薪资。
/* 档案名称: Demo982.sql */
USE NorthwindSQL
SELECT 部门,COUNT(*) AS 部门人数,AVG(目前薪资) AS 平均薪资
FROM 飞狐工作室
GROUP BY 部门
HAVING COUNT(*) >
(SELECT COUNT(*) FROM 飞狐工作室 WHERE 部门 = '业务部')
注：
n 子查询必须包含在一对小括号内。
“EXISTS”子查询的测试
n 使用EXISTS关键字，可检查是否至少有一条数据记录存在于子查询的查询结果中。
n 在子查询的SELECT语句选择列表中通常使用通配符星号*。
1. 查询出哪些公司并没有采购。
/* 档案名称: Demo980.sql */
USE NorthwindSQL
SELECT 客户.客户编号,客户.公司名称
FROM 客户
WHERE NOT EXISTS
(SELECT *
FROM 订货主档
WHERE 订货主档.客户编号 = 客户.客户编号)
“IN”子查询的测试
1. 查询出哪些公司并没有采购。
/* 档案名称: Demo983.sql */
USE NorthwindSQL
SELECT a.公司名称
FROM 客户 a
WHERE a.客户编号 NOT IN
(SELECT b.客户编号 FROM 订货主档 b)
2. 查询出哪些公司曾经采购过。（代码略）
附注：其实用EXISTS和IN可以达到相同的效果。
8.16. 本章命令一缆表
1．一条简单的SQL语句
Select 字段名/常量/SQL表达式 ――select 子句
From 表名列表 ――from 子句
2．查询结果
与数据库中的表一样，SQL查询的结果总是一个数据表。它也许只有一个字段、也许只有一条记录、也许只有一条记录一个字段的小表，当然也可以是一张空表。
SQL查询总是生成一个数据表的事实是非常重要的，它意味着查询结果可以作为一个表存回到数据库中，也也意味着两个类似查询结果可以组合以形成一个较大的查询结果表。最后，它意味着查询结果本身可以是进一步查询的目标。因此，关系数据库的表格式结构对SQL的关系查询功能具有协同增强的关系。表可以被查询，查询可以生成表。
3．简单查询
Select 子句命名必需的字段，from 子句命名包含它们的表。选择所有的字段（select*）,
* 星号作为“所有字段”的缩写。
4．重复的记录（distint）
Distinct用于清除重复的记录。
5．选择记录（where）
使用where来指定你想要检索的记录。
本质上，对于表中的每一条记录，搜索条件起到过滤器的作用。
6．搜索条件
比较测试（＝，<,<,>,<=,>,>=）：
把一个表达式的值和另一个表达式得值进行比较。
在SQL的3值逻辑下，一个搜索条件可以产生True，False或Null值。只有其搜索条件产生True的那些记录才被包括在查询结果中。
范围测试（between……and）：
测试一个表达式的值是否是在一个指定的值的范围内。
三个表达式的数据类型必须是可比较的。
组成员测试（in/not in）：
检查一个表达式的值是否匹配一组值中的一个。
模式匹配测试(Like)：
检查包含字符串数据的字段的值是否匹配一个指定的模式。
课件中说的比较详细，可参考。
Null值测试：
检查是否一个字段有一个Null（未知）值。
|
字段名 is |
|
not |
|
NULL |
|
NULL值测试（is Null）语法图 |
复合搜索条件（AND ,OR和NOT）
7．排序查询结果(order by 子句)
建议使用段号select sol表达式………
8．多表查询（连接）
课件详细。
9．汇总查询
SUM() 计算字段的总和。
AVG() 计算字段的平均值。
MIN() 查找字段中的最小值。
MAX()查找字段中的最大值。
COUNT()计算字段中值的数目。
COUNT(*)计算查询结果的记录数。
DISTINCT /ALL 消除重复记录。
SELECT COUNT(DISTINCT 客户编号) AS 客户数目
FROM 订货主档
WHERE 订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
|
字段3 |
GROUP BY 字段1，字段2，字段3
|
字段1 |
|
字段3
|
|
字段3 |
|
字段2 |
|
字段3
|
|
字段2 |
Select 分组字段/聚合函数 ――select 后面只能是。
11．分组搜索条件（having by 子句）
Having by 后面的条件字段可以是那些字段？
12．子查询
外部引用
Select city
From offices
Where target >(select sum(quota)
From salesaeps
Where rep_office=office)
子查询搜索条件
子查询比较测试（＝，>=,<,<=,>,<>）
组成员测试（in）
可不可以
Select
Where 一个子段 in (select 两个字段)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
