SQL操作知识总结（四）

6-2,组合聚集函数

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg

FROM Products;       COUNT(*) AS num_items,返回字段数目，MIN() AS price_min,prod_price字段的最小值。其他相同

7-1，分组数据

SELECT vend_id, COUNT(*) AS num_prods
FROM Products

GROUP BY vend_id;      分组数据按vend_id分组，结果类似下面：

vend_id   num_prods
------- ---------
BRS01         3
DLL01         4

FNG01         2  

解释：      GROUP BY vend_id，按   vend_id分组，数据库分为了三组，num_prods 各自的数目为3,4,2

注意：GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前

7-2，过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id

HAVING COUNT(*) >= 2;   过滤分组有点类似WHERE过滤，这段代码，是按cust_id分组，过滤数目大于等于2的

WHERE与HAVING连用：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id

HAVING COUNT(*) >= 2;    选择prod_price>= 4的且按vend_id>= 2分组，记住COUNT(*)是计数，比如实际应用，可以是

统计价格大于等于4元的，且销售的电脑品牌数量在两台以上的。  比如销售宏碁3台，戴尔销售1台，神舟销售2台，三个品牌价格都大于四元，那样就会筛选出两条数据来。宏碁与神舟。