1.
使用
group by
字段 进行分组的。
2.创建数据库:
create database day1;
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'电视',900);
insert into orders(id,product,price) values(5,'洗衣粉',90);
insert into orders(id,product,price) values(6,'洗衣粉',90);
3.练习
对订单表中商品归类后,显示每一类商品的总价
select product,sum(price) from orders group by product; 默认是一组
查询购买了几类商品,并且每类总价大于100的商品
select product from orders group by product having sum(price) > 100;
4.总结:
Having和where均可实现过滤,但在having可以使用聚集函数,where不能使用聚集函数,having通常跟在groupby后,它作用于分组
![](https://img-blog.csdnimg.cn/20210124214201408.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZ3NtaWxl,size_16,color_FFFFFF,t_70)