SQL必知必会学习笔记7-Kane

七.分组数据

• 创建分组
  使用select语句的GROUP BY子句分组

select vend_id,count(*) as num_prods
from products
group by vend_id;

按照vend_id 分组并排序，对每个vend_id计算count函数

• 过滤分组
  依然用group by 实现，选定想要的分组。而where语句只能过滤行而不能过滤分组。

这里用having语句代替where进行对组的过滤

select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2;
#功能与where语句一模一样

也可以where和having语句一起用

select vend_id,count(*) as num_prods
from products
where prod_price >= 4 #过滤掉价格小于4的行
group by vend_id
having count(*) >=2;