分组数据

分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

1）
SELECT vendid,COUNT(*) AS numprods
FROM products
GROUP BY vend_id;

2)WHERE过滤行，HAVING过滤分组

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

这里WHERE子句不起作用，因为过滤是基于分组聚集值而不是特定行值的

3）列出具有2个（含）以上、价格为10（含）以上的产品的供应商
SELECT vendid,COUNT() AS numprods
FROM products
WHERE prod_price >=10
GROUP BY vend_id
HAVING COUNT() >=2;

SELECT vendid,COUNT() AS numprods
FROM products
GROUP BY vend_id
HAVING COUNT() >=2;

4）


检索总计订单价格大于等于50的订单号和总计订单价格
SELECT ordernum,SUM(quantityitemprice) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantityitem_price) >= 50;

按总计订单价格排序输出：
SELECT ordernum,SUM(quantityitemprice) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantityitem_price) >= 50
ORDER BY ordertotal;

5）