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; 

得到的结果相同！