汇总与分组查询与单行子查询和多行子查询
9.2.1 汇总数据
SQL提供了5个汇总函数用于在表的列上进行算术运算，这5个汇总函数是：Count、Sum、Avg、Max、Min。Count和Sum尽管在表面上比较相近，但其作用完全不同。Count 用于计算表中数据的行数，而Sum用于计算数值项相加的和。Avg用于计算表中一个字段的平均值，Max用于得到最大值，Min用于得到最小值，如表8.7所示。
表8.7 汇总函数列表
当使用汇总函数时，默认情况下，汇总信息包含所有的指定行。在某些情况下，结果集包含非惟一的行。可使用汇总函数的DISTINCT 选项筛选出非惟一的行。
任务9.12
问题描述：计算产品表中的行数。
解决方案：
SELECT Count(*)
FROM Products
分析与讨论：
汇总函数Count(*)计算表的行数，包括含有NULL值的行。
注意：除非和ＧROUP BY相连，在查询的项中，SELECT后面的汇总函数不能和字段名一起使用。如下面的代码是错误的：
SELECT ProductName ,Count(*) FROM Products
任务9.13
问题描述：统计订单数目。
解决方案：
SELECT Count( Distinct OrderID) AS 订单数目
FROM [Order Details]
分析与讨论：
汇总函数Count(OrderID)计算OrderID列的值的个数，OrderID列的NULL值不包含在内。
汇总函数Count( Distinct OrderID) 计算OrderID列的值的个数，OrderID列的NULL值不包含在内，如果多行OrderID值相同，则只取一行参入计数。
考虑下面两个查询语句的不同。
SELECT C ount(OrderID) AS 订单数目 FROM [Order Details]
SELECT Count( Distinct OrderID) AS 订单数目 FROM [Order Details]
第一个查询语句计算订单明细表中所有的订单数，包括重复的订单，第二个查询只计算
不同的订单。
任务9.14
问题描述：查询所有订单的销售额。
解决方案：
SELECT SUM(UnitPrice *Quantity *(1-Discount)) AS 销售额
FROM [Order Details]
分析与讨论：
以上查询使用汇总函数SUM对计算列UnitPrice *Quantity *(1-Discount)求和，求出所有订单的销售额。
9.2.2 分组查询
对于一个查询的输出结果，还可以基于指定的列对它们进行分组操作。通过使用GROUP BY关键字来对行依照指定列相同的值进行分组，这样就可以实现对每组记录进行计算，并将每一组记录合并或汇集为一个记录。像这样使用GROUP BY关键字的查询叫做分组查询，分组查询常和汇总函数一起使用。
1.按一列的值分组
按一列的值分组，就是对一列使用GROUP BY，这样列值相同的行归为一组，然后就可以在SELECT 子句
任务9.15
问题描述：计算图9.1所示的表(a)订单明细表中每个不同订单订购的产品总数。
（a）(b)
图9.1 对OrderID列分组查询
解决方案：
SELECT OrderID, SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
分析与讨论：
要计算出图9.1所示的表(a)每个不同订单订购的产品总数，可对表(a)使用GROUP BY 关键字对OderID列进行分组，DBMS会首先按照OderID值把所有的行排序，然后将所有OderID列值相同的行归成一组，针对于每一个不同的OderID值，都会有相应的一个组，这样就得到6组，然后对各组的Quantity列求和，就得到各组的订购产品的总数，并将每一组都合并为一条记录（如图9.1（b）所示）。
当使用GROUP BY时，只有在GROUP BY子句中出现的列和汇总函数可以出现在SELECT指定的列表中，以下查询会产生错误：
SELECT OrderID, Quantity,SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
2. 按多列的值分组
按多列的值分组，就是对多列使用GROUP BY，这几列值相同的行归为一组。
任务9.16
问题描述：对产品表，计算每一个供应商对每一类别供应多少个不同的产品。
解决方案：
USE Northwind
SELECT SupplierID,CategoryID,COUNT(*)AS 不同产品数
FROM Products
GROUP BY SupplierID,CategoryID
ORDER BY SupplierID
(a) (b)
图9.1对SupplierID和CategoryID分组查询
分析与讨论：
以上代码对SupplierID,CategoryID列分组，这样在Products表中，SupplierID,CategoryID 列值相同的行归为一组，如图9.2(a)所示，1-2行归为第一组，第3行为第二组，4-7行归为第三组，第8、9行归为四组，第7行归为第五组。第一组有二行，第二有一行，第三组有四行，第五组有一行。在分组查询结果中，每一组都汇集为一行，汇总函数提供的是有关每个组（而不是各行）的信息（如图9.2(b)）。
当使用GROUP BY指定多列时，列名之间用逗号分隔，不能使用在SELECT 列表中定义的列别名来指定组合列。
在SELECT 列表中所有未包含在汇总函数中的列都应该包含在GROUP BY 子句中。包含在GROUP BY 子句中的列不一定必须包含在SELECT 列表中。
注意，GROUP BY指定的列不能是类型为text、ntext 和image 的列。
ORDER BY可以应用于分组查询中。
3．使用HAVING筛选分组后的行
HAVING 子句是对GROUP BY 子句设置条件，就是对组设置条件，它和WHERE类似，但WHERE 搜索条件在进行分组操作之前应用；而HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与WHERE 语法也类似，但HAVING 可以包含汇总函数。HAVING 子句可以引用选择列表中显示的任意项。
任务9.17
问题描述：查询订购产品数量大于100的的订单的订单号。
解决方案：
SELECT OrderID, SUM(Quantity) AS 产品总数
FROM [Order Details]
GROUP BY OrderID
HAVING SUM(Quantity)>100
分析与讨论：
以上查询对分组之后的行进行筛选，只有产品总数列的值大于100的行才包含在查询结果中。如图9.1(b)中，只有第5、6行包含在查询结果中。
当设置HAVING 搜索条件时可以包含汇总函数，但不能使用在SELECT 列表中定义的列别名，如使用以下HAVING 子句是错误的：
HAVING 产品总数>100
使用HAVING 过滤分组:
1）行已经被分组。
2）使用了汇总函数。
3）满足HAVING 子句中条件的组将被显示
4．将WHERE、GROUP BY 和HAVING一起使用
如果一个查询包含WHERE、GROUP BY 和HAVING 子句，应用WHERE、GROUP BY 和HAVING 子句的顺序如下：
1）WHERE 子句用来筛选FROM 子句中指定的操作所产生的行。
2）GROUP BY 子句用来分组WHERE 子句的输出。
3）HAVING 子句用来从分组的结果中筛选行。
对于可以在分组操作之前或之后应用的任何搜索条件，在WHERE 子句中指定它们会更有效，这样可以减少必须分组的行数。应当在HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。
任务9.17
问题描述：查询单价格超过$150 且平均订定购数量大于5 的产品
解决方案：
SELECT ProductID ,AVG(Quantity) AS 平均订购数量
FROM "Order Details"
WHERE UnitPrice >= 150.00
GROUP BY ProductID
HAVING AVG(Quantity) > 5
ORDER BY ProductID ;
分析与讨论：
以上分组查询首先选择Order Details表中UnitPrice值大于或等于150.00的行，然后将选择出的行按ProductID的值分组，每一组都汇集为一行，再从分组的结果中，选择AVG(Quantity) 值大于5的行。也就是说组和汇总值是在消除价格低于$150 且平均订购数量低于或等于5的产品之后得出的。
注意，WHERE 放在GROUP BY的前面，HAVING放在GROUP BY的后面。
不能在WHERE 子句中使用汇总函数，但可以在HAVING 子句中使用汇总函数。
单行子查询和多行子查询
子查询只返回单个值而不是值列表的子查询叫单行子查询。如果子查询返回零个或多个值列表，则该子查询叫多行子查询。
单行子查询只能够由单行比较操作符（=、< >、>、> =、<、!>、! < 或< =）引入。如果由单行比较操作符引入的子查询返回多行值，则会产生错误。
多行子查询只能够由表9.多行比较操作符引入。
1. 使用SOME或ANY的子查询
SOME 是与 ANY 等效的 ISO 标准。其一般格式为：
expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ SOME | ANY } ( 子查询 )
它是将条件表达式中左边的值expression与子查询返回的任意一个值比较，只要其中任何一个比较返回为真，则整个条件表达式的值为真。
任务9.21
问题描述：运用子查询查找单价比Beverages类（类别ID为1）的产品最高价格低的所有其它类的产品。
解决方案：
USE Northwind
SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice
FROM Products p1
WHERE p1. UnitPrice (SELECT p2.UnitPrice FROM Products p2 WHERE p2.CategoryID =1) AND p1.CategoryID !=1 分析与讨论： 1.SOME 和ANY等效，它是将p1. UnitPrice与子查询的结果集（该结果集是单列值列表）中的任何一个值进行比较，只要其中任何一个比较返回值为TRUE，则使用SOME 或ANY的条件表达式为TRUE，否则为FALSE。也就是说： >ANY 表示至少大于一个值，即大于最小值，返回值为TRUE,否则为FALSE。 =ANY 运算符与IN 等效。它表示只要和其中任何一个值相等，返回值为TRUE,否则为FALSE。 2.以上代码子查询的结果集是类别ID值为1的产品的单价列表。外部查询的查询条件是产品的UnitPrice至少小于别ID值为1的产品的单价中的一个并且该产品不属于Beverages 类（Beverages类的类别ID为1）。也就是说外部查询是查找单价比Beverages类（类别ID 为1）的产品最高价格低的所有其它类的产品。以上代码和下面代码等效：SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice FROM Products p1 WHERE p1. UnitPrice < (SELECT MAX(p2.UnitPrice) FROM Products p2 WHERE p2.CategoryID =1) AND p1.CategoryID !=1 3.如果子查询不返回任何值，那么整个查询将不会返回任何值。 4. SOME或ANY的子查询必须是返回单列结果集的子查询，且返回列的数据类型必须与SOME或ANY运算符左边的表达式的的数据类型相同。 5. =ANY 运算符与IN 等效,但是，< >ANY 运算符则不同于NOT IN：< >ANY 表示不等于a，或者不等于b，或者不等于c。NOT IN 表示不等于a、不等于b 并且不等于c。<>ALL 与NOT IN 表示的意思相同 2. 使用ALL的子查询 使用ALL运算符的一般格式为： expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( 子查询 ) 它是将条件表达式中运算符左边的值expression与子查询返回的每一个值进行比较，如果每一个比较都为TRUE，则整个条件表达式的值为TRUE。否则整个条件表达式的值为FALSE。 任务9.22 问题描述：查找同类产品中价格最高的产品。 解决方案： USE Northwind SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice FROM Products p1 WHERE p1. UnitPrice >ALL (SELECT p2.UnitPrice FROM Products p2 WHERE p1.CategoryID = p2.CategoryID AND p1.UnitPrice<>p2.UnitPrice) 分析与讨论： 1.ALL运算符是将p1. UnitPrice与子查询的结果集（该结果集是单列值列表）中的每一个值进行比较，如果每一个比较都为TRUE，则使用ALL的条件表达式为TRUE，否则为FALSE。也就是说： >ALL 表示大于每一个值，即大于最大值，返回值为TRUE,否则为FALSE。 < >ALL 运算符与NOT IN 等效。它表示和其中每一个值都不相等，返回值为TRUE,否则为FALSE。 2. 以上查询的子查询无法独立于外部查询进行计算，它需要p1.CategoryID 值，但是此值随DBMS 检查p1表中的不同行而改变。上面代码中，外部查询依次选择表p1的行，即依次选择某个产品，子查询为外部查询中的选择行查询所选择产品的同类产品的价格。对于p1的每一行，如果该行的UnitPrice大于子查询查询出的同类产品的其它产品的每一个价格，则该行将放入查询结果中。也就是说如果该行的UnitPrice是同类产品中价格最高的，则该行将放入查询结果中。以上代码和下面代码等效： USE Northwind SELECT p1.ProductName,p1.CategoryID,p1.UnitPrice FROM Products p1 WHERE p1. UnitPrice >SOME (SELECT MAX(p2.UnitPrice) FROM Products p2 WHERE p1.CategoryID = p2.CategoryID AND p1.UnitPrice<>p2.UnitPrice) 3.如果子查询不返回任何值，那么整个查询将不会返回任何值。 4. ALL的子查询必须是返回单列结果集的子查询，且返回列的数据类型必须与ALL运算符左边的表达式的的数据类型相同。