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必知必会》