数据库（三）——SQL语句二 分组过滤

1. 组合聚集函数

select COUNT(*) as num_items ,MIN(prod_price) as price_min,MAX(prod_price) as price_max,AVG(prod_price) as price_avg from Products;

分组数据 Group by 和 having

1. 创建分组

select vend_id,COUNT(*) as num_prods from Products Group by vend_id;

group by 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组

2. 过滤分组

select cust_id,count(*) as orders from orders group by cust_id having count(*)>= 2;

//具有两个以上产品，其价格大于等于4的供应商
select vend_id,count(*) as num_prods from Products where prod_price>= 4 group by vend_id having count(*)>= 2;

3. 分组和排序 group by 和 order by

select order_num,Count(*) as items from OrderItems group by order_num having COUNT(*)> = 3;

select order_num,Count(*) as items from OrderItems group by order_num having COUNT(*)> = 3 order by items,order_num;

4. select子句顺序

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否