分组数据

用GROUP BY 跟 HAVING子句,分组数据来汇总表内容子集。

创建分组

分组在SELECT语句的GROUP BY子句中建立。

  1. mysql> SELECT vend_id, COUNT(*) AS num_prods
  2. -> FROM Products
  3. -> GROUP BY vend_id;
  4. +---------+-----------+
  5. | vend_id | num_prods |
  6. +---------+-----------+
  7. | BRS01 | 3 |
  8. | DLL01 | 4 |
  9. | FNG01 | 2 |
  10. +---------+-----------+
  11. 3 rows in set (0.01 sec)

解释: SELECT语句指定两个列,vend_id 包含供应商ID,为num_prods 计算字段结果,GROUP BY 子句指示 vend_id 排序并分组数据,

GROUP BY子句重要规则:

  • 包含任意数目的列,
  • 如果在GROUP BY 子句中套入分组,数据将会最后规定的分组上进行总汇。
  • GROUP BY 子句中列出的没列都必须是检索的列,有效的表达式,不能聚集函数。
  • 大多数SQL不允许GROUP BY 带有长度可变的数据类型(文本,备注型字段)
  • 除聚集计算语句外,SELECT 语句中,每个列都必须在GROUP BY子句中给出。
  • 如果分组带有NULL值,将作为一个分组返回,如果多个将成一组。
  • GROUP BY 子句必须出现在WHERE子句之后,

    过滤分组

过滤分组规定包含哪些分组,排除哪些分组,用HAVING子句,与WHERE子句类似,唯一差别的是WHERE用来过滤行,HAVING过滤分组。也可以说HAVING在数据分组后过滤,WHERE在数据分组前进行过滤。

HAVING 支持所有WHERE的操作符。

  1. mysql> SELECT cust_id, COUNT(*) AS orders
  2. -> FROM Orders
  3. -> GROUP BY cust_id
  4. -> HAVING COUNT(*) >= 2;
  5. +------------+--------+
  6. | cust_id | orders |
  7. +------------+--------+
  8. | 1000000001 | 2 |
  9. +------------+--------+
  10. 1 row in set (0.00 sec)

解释: 过滤出两个以上订单的分组

WHERE与HAVING子句结合使用

  1. mysql> SELECT vend_id, COUNT(*) AS num_prods
  2. -> FROM Products
  3. -> WHERE prod_price >= 4
  4. -> GROUP BY vend_id
  5. -> HAVING COUNT(*) >= 2;
  6. +---------+-----------+
  7. | vend_id | num_prods |
  8. +---------+-----------+
  9. | BRS01 | 3 |
  10. | FNG01 | 2 |
  11. +---------+-----------+
  12. 2 rows in set (0.00 sec)

解释: 第一行使用聚集函数,WHERE子句过滤除所有prod_price少于4的行,按vend_id分组,HAVING子句过滤计数2以上分组。

去掉WHERE 过滤

  1. mysql> SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id HAVING COUNT(*) >= 2;
  2. +---------+-----------+
  3. | vend_id | num_prods |
  4. +---------+-----------+
  5. | BRS01 | 3 |
  6. | DLL01 | 4 |
  7. | FNG01 | 2 |
  8. +---------+-----------+
  9. 3 rows in set (0.01 sec)

过滤出销售产品在4个,且价格是4一下的。

分组和排序

GROUP BY 与 ORDER BY区别

  • GROUP BY

    • 排序产生的输出
    • 任意列都可以使用
    • 可以选择是否与聚集函数一起使用
  • ORDER BY

    • 分组行,输出可能不是分组循序
    • 只可能使用选择列或表达式,且必须使用每个列表达式
    • 如果与聚集函数一起用,则必须使用
      注意: 不用依赖于GROUP BY 排序,应该使用GROUP BY 时,也该处ORDER BY子句。

检索除3个或以上的物品订单号与订购物品数目:

  1. mysql> SELECT order_num, COUNT(*) AS items
  2. -> FROM OrderItems
  3. -> GROUP BY order_num
  4. -> HAVING COUNT(*) >= 3;
  5. +-----------+-------+
  6. | order_num | items |
  7. +-----------+-------+
  8. | 20006 | 3 |
  9. | 20007 | 5 |
  10. | 20008 | 5 |
  11. | 20009 | 3 |
  12. +-----------+-------+
  13. 4 rows in set (0.00 sec)

按订购物品数目排序输出。

  1. mysql> SELECT order_num, COUNT(*) AS items
  2. -> FROM OrderItems
  3. -> GROUP BY order_num
  4. -> HAVING COUNT(*) >=3
  5. -> ORDER BY items, order_num;
  6. +-----------+-------+
  7. | order_num | items |
  8. +-----------+-------+
  9. | 20006 | 3 |
  10. | 20009 | 3 |
  11. | 20007 | 5 |
  12. | 20008 | 5 |
  13. +-----------+-------+
  14. 4 rows in set (0.00 sec)

解释: GROUP BY 子句用来分组数据, COUNT(*)函数返回订单中物品数目,HAVING 子句过滤数据,返回3个或3个以上的物品订单,ORDER BY最后排序输出。

SELECT子句顺序

子句 说明 是否必须使用
SELECT 要返回的列或表达式
FROM 从中检索数据 仅从 表中选择数据时使用
WHERE 行级过滤
GROUP BY 分组说明 仅按组计算聚集使用
HAVING 组级过滤
ORDER BY 输出排序顺序

原文: https://cxiaodian.gitbooks.io/mysql/content/chapter9.html