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;