1.创建分组(group by)
示例:查询一共有多少条数据,按照vend_id的不同来分组显示
select vend_id,count( * ) from products group by vend_id;
2.创建摘要(with rollup)
示例:同上,上面不带总计,需要加上对应关键字
select vend_id,count( * ) from products group by vend_id with rollup;
3.过滤分组(having)类似where的功能,只是在分组中不能使用where
示例:过滤出每个分组条数大于二的数据
根据用户id进行分组,条件是返回分组中有数据条数等于2的
select id,count( * ) from orders group by id having count( * ) = 2;
4.分组和排序
示例:根据订单编号分组,计算出每个订单编号的下的商品总价格,且总价格要大于50,并且按照总价格的大小进行从小到大订单排序;
select order_num,sum( price * quantity ) as ordertotal from orderitems group by order_num having sum( price * quantity ) > 50 order by ordertotal;
5.select 语句的执行顺序
- select
- from
- where
- group by
- having
- order by
- limit