SELECT语句之分组数据

1 数据分组

SQL聚集函数可以用来汇总数据，例如下面的例子返回供应商DLL01提供的产品数目。

SELECT COUNT(*) AS num_prods

FROM Products

WHERE vend_id = ‘DLL01’;

输出：

num_prods

———

4

如果现在要返回每个供应商提供的产品数目该怎么办？这时候就需要用到“分组”的功能了。“分组”允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

2 创建分组

“分组”由SELECT语句的GROUP BY子句建立。

SELECT vend_id, COUNT(*) AS num_prods

FROM Products

GROUP BY vend_id;

输出：

vend_id      num_prods

———       ———

BRS01        3

DLL01        4

FNG01        2

上面的GROUP BY子句指示DBMS按vend_id分组数据，这导致对每个vend_id计算COUNT(*)而不是整个表只计算一次。从输出可以看到，供应商BRS01有3个产品，DLL01有4个产品，FNG01有2个产品。

在具体使用GROUP BY子句之前，需要了解一些重要规定：

1. GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

2. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。

3. 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如本文或备注型字段）。

4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

3 过滤分组

例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。所以WHERE子句不能完成这个任务，因为WHERE过滤指定的是列而不是分组，WHERE没有分组的概念。SQL为此目的提供了HAVING子句，HAVING子句类似于WHERE。

SELECT cust_id, COUNT(*) AS orders

FROM Orders

GROUP BY cust_id

HAVING COUNT(*) >= 2;

输出：

cust_id   orders

——-   ——-

10000001  2

HAVING子句过滤了COUNT(*) >= 2(具有两个以上订单)的那些分组。

有没有在一条语句中同时使用WHERE和HAVING子句的的需要呢？确实有。例如想列出具有两个以上、价格为4以上的产品的供应商：

SELECT vend_id, COUNT(*) AS num_prods

FROM Products

WHERE prod_price >= 4

GROUP BY vend_id

HAVING COUNT(*) >= 2;

输出：

vend_id       num_prods

———        ———-

BRS01         3

FNG01        2

HAVING和WHERE非常类似，如果不指定GROUP BY，则大多数DBMS将它们作为相同的东西对待。不过，我们要自己区分这一点。应该仅在与GROUP BY子句结合时才使用HAVING，而WHERE子句用于标准的行级过滤。

4 分组和排序

经常会发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。所以如果需要对分组数据进行排序，就应该明确提供ORDER BY子句。

SELECT order_num, COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*) >= 3

ORDER BY items, order_num;

输出：

order_num     items

———             ——

20006              3

20009              3

20007              5

20008             5

上面语句检索包含3个或3个以上物品的订单号和订购物品的数量，并且检索的结果先按物品数量排序，再按订单号排序。

5 SELECT子句顺序

SELECT子句在使用时必须遵循以下次序：

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

以上内容整理自《SQL必知必会》