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