分组查询

求出每名销售人员的平均订单大小。

SELECT REP,AVG(AMOUNT)

   FROM ORDERS

 GROUP BY REP

 

工作原理：

1.SQL将订单分成几组。在每一组内，所有的订单在REP字段有同样的值。

2.对于每一组，SQL计算组中所有记录的AMOUNT字段的平均值，并生成一个汇总查询结果记录。这一纪录包含这一组的REP字段的值和计算好的平均订单大小。

 

 

分组查询的限制：

分组字段必须是在查询的FROM子句中命名的表的实际字段，不能基于计算的表达式的值来对记录进行分组。

对出现在分组查询的选择列表中的项，也有限制。对于每一记录组来说，在选择列表中的所有项必须有一个值。

这个值可以是：一个常量；一个字段函数；一个分组字段，按照定义，这个分组字段在组中的每一记录有同样的值；涉及上述各项组合的表达式。

 

在实践中，分组查询在它的选择列表中将总是包括一个分组字段和一个字段函数。如果没有字段函数出现，那么可以使用不含GROUP BY的SELECT DISTINCT更简单的表述查询。相反，如果在查询结果中没有包含一个分组字段，就不能知道查询结果中哪一记录来自哪一组。

 

分组查询的另一限制是，当分析分组查询的合法性时，SQL忽略关于主键字和外键字的信息。

 

SELECT EMPL_NUM,NAME,SUM(AMOUNT)

   FROM ORDERS,SALESREPS

WHERE REP = EMPL_NUM

GROUP BY EMPL_NUM

 

Error：“NAME” not a GROUP BY expression

 

修改：

 

SELECT EMPL_NUM,NAME,SUM(AMOUNT)

   FROM ORDERS,SALESREPS

WHERE REP = EMPL_NUM

GROUP BY EMPL_NUM,NAME

 

即对于每个分组来说销售人员的姓名必须是单值的。

分组字段中的NULL值

当NULL值出现在一个分组字段中时，它将产生一个特殊的问题。如果字段值是未知的，那么这一纪录应放入哪一组中？在WHERE子句中，当比较两个不同的NULL值时，结果是NULL，即，两个NULL值不被认为是相等的。将相同的惯例应用到GROUP BY子句中，会强迫SQL把带有一个NULL分组字段的每一记录放到一个不同的组中。

 

求出订单综合超过30000的销售人员的平均订单大小。

SELECT REP,AVG(AMOUNT)

FROM ORDERS

GROUP BY REP

HAVING SUM(AMOUNT) > 30000.00

 

对具有两个或更多销售人员的销售点，计算在此工作的所有销售人员的总的定额和总的销售量。

SELECT CITY,SUM(QUOTA),SUM(SALESREPS.SALES)

FROM OFFICES,SALESREPS

WHERE OFFICE = REP_OFFICE

GROUP BY CITY

HAVING COUNT(*) >= 2

 

显示订单总量超过其现货量75%的每项产品的单价、现货量和订单总量。

SELECT DESCRIPTION,PRICE,QTY_ON_HAND,SUM(QTY)

FROM PRODUCTS,ORDERS

WHERE MFR = MFR_ID

AND PRODUCT = PRODUCT_ID

GROUP BY MFR_ID,PRODUCT_ID,DESCRIPTION,PRICE,QTY_ON_HAND

HAVING SUM(QTY) > (.75 * QTY_ON_HAND)

ORDER BY QTY_ON_HAND DESC

执行步骤：

1.连接ORDERS和PRODUCTS表，以找到指定的每项产品的说明、单价和现货量

2.按生产商和产品ID对生成的结果记录进行分组。

3.去除订货量（组中所有订单的QTY字段的综合）小于现货量的75%的组。

4.计算每组的订货量

5.为每组生成一个查询结果汇总记录

6.排序查询结果，以便现货量最大的产品第一个出现。

 

分组搜索条件上的限制

HAVING子句用于把记录组包括进查询结果或从查询结果中去除，所以它指定的搜索条件必须是作为一个整体应用于组而不是应用于各个记录。这意味着出现在HAVING子句中搜索条件内的项可以是：

一个常量

一个字段函数

一个分组字段

一个包含上述各项组合的表达式

在实际中，在HAVING子句中的搜索条件总是包括至少一个字段函数。如果没有，搜索条件会被移到WHERE子句中并被应用到各个记录。区分搜索条件是属于WHERE子句还是属于HAVING子句的最容易的方法是记住这两个子句是怎样使用的：

WHERE子句应用于各个记录，所以它包含的表达式一定能进行单个记录计算

HAVING子句应用于记录组，所以它包含的表达式一定能进行记录组计算

 

HAVING子句的搜索条件可以生成以下三种结果之一：

如果搜索条件是TRUE，那么这个记录组被保留，并且它向查询结果提供汇总记录。

如果搜索条件是FALSE，那么这个记录组被去掉，并且不向查询结果提供汇总记录

如果搜索条件是NULL，那么这个记录被去掉，并且它不向查询结果提供汇总记录。

 

下一篇 子查询和查询表达式