1、创建分组
GROUP BY
SELECT ven_id,COUNT(*) AS num_prods FROM products GROUP BY ven_id;
注意:
GROUP BY可以包含任意数目的列
GROUP BY列出的每个列都必须是检索列或者有效的表达式(但不能是聚集函数)。如果在SELECT语句中使用表达式,则必须在GROUP BY中指定相同的表达式,不能使用别名。
除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。
GROUP BY必须出现在WHERE语句之后,ORDER BY语句之前。
2、过滤分组
HAVING过滤分组,WHERE过滤行。
SELECT cust_id ,COUNT(*) AS orders
FROM orders GROUP BY cust_id
HAVING COUNT(*) >=2;
COUNT(*) >=2--过滤2个以上的订单分组
例子:列出具有2个(含)以上、价格为10(含)以上的产品的供应商
SELECT cust_id ,COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10
GROUP BY ven_id
HAVING COUNT(*) >=2;
3、分组和排序
例子:检索总订单价格大于等于50的订单号和总计订单价格
SELECT order_num,SUM(quantiy*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantiy*item_price)>=50;
为按总计订单价格排序输出,需要添加ORDER BY语句,如下:
SELECT order_num,SUM(quantiy*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantiy*item_price)>=50
ORDER BY ordertotal;