MySQL必知必会(数据分组,Group by和Having子句, Select子句的顺序)

SELECT vend_id, COUNT(*) AS num_prods FROM products #GROUP BY子句可以包含任意数目的列，多行NULL值将分为一组
GROUP BY vend_id; #GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

SELECT cust_id, COUNT(*) AS orders FROM orders #Having非常类似于Where，都是起过滤作用，唯一的差别是
GROUP BY cust_id HAVING COUNT(*) >= 2; #Where过滤行，Having过滤分组。

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

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal; #Group by的输出可能不是分组的顺序

Select子句及其顺序