MySQL燕十八老师课程笔记:第九课:having筛选

回顾之前做过的一道题:查询本店价比市场价省的钱,并且要求省钱200元以上的取出来

select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods where (market_price-shop_price) > 200;

# 注意:最后的那个:不能用where discount>200,因为这个已经是结果了,MySQL语句不能对结果进行操作。

使用having:

select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods where 1
having discount > 200;

思考:having与group 的综合运用!
1.查询该店的商品比市场价所节省的价格

select goods_id,goods_name,market_price-shop_price as discount from goods;

2.查询每种商品所积压的货款(提示:商品*价格)

select goods_id,shop_price,goods_number,goods_number*shop_price as huokuan
from goods;

3.查询该店积压的总货款

# 即每个商品积压货款之和
select sum(goods_number*shop_price) from goods;

4.查询该店每个栏目下面积压的货款

# 只需要在3的基础上group by 即可
select cat_id,sum(goods_number*shop_price) from goods group by cat_id;

5.查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)

# 用where实现
select goods_id,market_price,shop_price,
market_price-shop_price as discount from goods
where (market_price-shop_price)>200;

# 用having实现
select goods_id,market_price,shop_price,
market_price-shop_price as discount from goods
having discount>200;

6.查询积压货款超过2万元的栏目,以及该栏目积压的货款

# 用group by 和having 配合
select cat_id,sum(goods_number*shop_price) as zongkuokuan from goods
group by cat_id 
having zonghuokuan>20000;

7.where-having-group 综合练习题:
有如下表及数据:
在这里插入图片描述
要求:查询出2门及2门以上不及格者的平均成绩。

# 我的语句::(但是好像错了)
select name,avg(score) from result group by name having count(score<60)>=2;
# 该语句也可以写为:
select name,avg(score)count(score<60) as guakeshu
 from result group by name having guakeshu>=2;
# 错误暴露:
insert into result 
values
('赵六','语文',100),
('赵六','数学',99),
('赵六','品德',98);

在这里插入图片描述
在这种情况下再次select:

在这里插入图片描述
赵六的成绩很好,但在我这种算法下仍然被划归为两门及两门以上成绩不合格的人。

原因:因为count函数,括号里面的数是几对它的计算结果没有影响——count(0) 和 count(1) 的结果都一样。

正解!!

# 第一步:查询所有的平均分
select name,avg(score) from result group by name;

在这里插入图片描述

# 下一步:再想办法计算出每个人的挂科情况
select name,subject,score,score<60 as g from result;

在这里插入图片描述
如上,挂科数目就是g的sum结果。

# 综合上面两个语句:
select name,avg(score),sum(score<60) as gks
from result group by name;
# 每个人的平均分及挂科数已算出,再having筛选一下即可
select name,avg(score),sum(score<60) as gks 
from result group by name having gks>=2;
(其实就是把我的思路里的count改成sum 呗~

在这里插入图片描述
这个思路是逆向思维,先查出所有人的平均,再筛选。
正向思维考虑:1.先找出谁的挂科数>=2,找到这些人,再求这些人的平均分。

# 先找挂科数>=2的那些人
select name,count(1) from result where score<60 group by name;

在这里插入图片描述

select name,count(1) as gks from result where score<60  
 group by name having gks>=2;

在这里插入图片描述

# 对上面那条语句的结果进行筛选,求出符合条件的姓名:
select name from (select name,count(1) as gks from result where score<60  
 group by name having gks>=2) as tmp;
 
 # 再筛选:
 select name,avg(score) from result where name in
  (select name from 
  (select name,count(1) as gks from result where score<60  
  group by name having gks>=2) as tmp) 
 group by name; 

在这里插入图片描述
得到的结果相同!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值