mysql基础7——where与having的差异

where直接对表中的字段进行限定 筛选结果

having需要根据分组关键字group by一起使用，通过对分组字段和分组计算函数限定 筛选结果

distinct 字段 ， 返回字段中所有不同的值

distinct 字段;

where

如果需要对关联表进行查询，where字段执行时先对where条件进行筛选 用筛选后较小的数据集进行连接 这样连接过程中占用的资源较少，执行效率较高

select distinct b.goodsname from demo.transactiondetails as a join demo.membermaster as b on (a.itemnum=b.itemnum) where a.salevalues>50;

a.首先使用where条件进行查询

select * from demo.transactiondetails as a  where a.salevalues>50;

b.然后关联查询

select a.*, b.goodsname from demo.transactiondetails as a join demo.membermaster as b on (a.itemnum=b.itemnum) where a.salevalues>50;

c.然后查询商品名称 （去重）

select distinct b.goodsname from demo.transactiondetails as a join demo.membermaster as b on (a.itemnum=b.itemnum) where a.salevalues>50;

group by 对数据分组 方便对组内的数据进行统计计算

查询每天每个收银员的销售数量和销售金额

select a.transdate,c.operatorname,d.goodsname,b.quantity,b.price,b.salesvalue 
from demo.transactionhead as a 
join demo.transactiondetails as b on (a.transactionid=b.transactionid) 
join demo.operator as c on (a.operatorid=c.operatorid) 
join demo.goodsmaster as d on (b.itemnum=d.itemnum);

查询每天的销售数量和销售金额  (按照日期“transdate”字段进行分组)

select a.transdate,sum(b.quantity),sum(b.salesvalue) 
from demo.transactionhead as a 
join demo.transactiondetails as b on (a.transactionid=b.transactionid) 
group by a.transdate;

查询每天每个收银员的销售数量和销售金额，可以按照2个字段进行分组和统计 分别是 "transdate"字段和"operatorname"字段

select a.transdate,c.operatorname,sum(b.quantity),sum(b.salevalues)
from demo.transactionhead as a
join demo.transactiondetails as b on (a.transactionid=b.transactionid)
join demo.operator as c on (a.operatorid=c.operatorid)
group by a.transdate,c.operatorname;

having

要把所有信息都准备好，包括从关联表中获取的数据，对数据集进行分组，然后通过having条件筛选

查询单笔销售金额超过50元的商品

select b.goodsname
from demo.transactiondetails as a
join demo.goodsmaster as b on (a.itemnum=b.itemnum)
group by b.goodsname;
having max(a.salesvalue)>50;

分四步

a.将两表连接获取数据

select a.*,b.*
from demo.transactiondetails as a
join demo.goodsmaster as b on (a.itemnum=b.itemnum);

b.将上述结果集按照商品名称分组

select a.*, b.*
from demo.transactiondetails as a
join demo.goodsmaster as b on (a.itemnum=b.itemnum)
group by b.goodsname;

c.对分组后的结果集进行筛选 即筛选出salesvalue最大值大于50的组

select a.*, b.*
from demo.transactiondetails as a
join demo.goodsmaster as b on (a.itemnum=b.itemnum)
group by b.goodsname;
having max(a.salesvalue)>50;

d.返回商品名称

select b.goodsname
from demo.transactiondetails as a
join demo.goodsmaster as b on (a.itemnum=b.itemnum)
group by b.goodsname;
having max(a.salesvalue)>50;

where与having的区别

1）如果需要通过连接从关联表中获取所需要的数据时，where先筛选后连接，having先连接后筛选   where比having更高效 

2）where可以直接使用表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件；having 必须要与group by配合使用，可以把分组计算的函数和字段作为筛选条件

例如 查询哪个收银员在哪天卖了2单商品 必须先分组才能筛选查询

select a.transdate,c.operatorname
from demo.transactionhead as a
join demo.transactiondetails as b on (a.transactionid=b.transactionid)
join demo.operator as c on (a.operatorid=b.operatorid)
group by a.transdate,c.operatorname
having count(*)=2;

where与having不是互相排斥的 可以在一个查询里面同时使用where和having

原文地址：https://blog.csdn.net/qq_43773652/article/details/137377366

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！