mysql必知必会（八）：过滤分组

分组 GROUP BY

GROUP BY位于WHERE之后，ORDER BY 之前
在产品表中根据供应商ID(vend_id)进行分组

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

过滤分组

1.WHERE过滤指定的是行而不是分组，要对分组进行过滤可以使用HAVING
2.WHERE在分组前进行过滤，HAVING在分组后进行过滤，WHERE排除的行不包括在分组中
在订单表中根据cust_id进行分组，然后过滤出2个以上的订单

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

列出具有2个（含）以上、价格为10（含）以上的产品的供应商

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2;

分组排序

查询出总订单价格大于等于50的订单号和总订单价格，并按照总订单价格升序排列

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal>=50 ORDER BY ordertotal;