MySQL 中 having 筛选

在之前的文章中有过一个例子说的，筛选出本店价格比市场价便宜 200 元以上的商品，当时写的语句如下：

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

其中 where 后面必须是 (market_price - shop_price) 而不能是 discount。因为先使用 where 查询出来以后才有的 discount ，where 条件如果引用列，只能是表中已有的列。如果要对结果集再次筛选，则需要用到 having。上面的语句就可以改成：

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

例子

1. 查询每种商品所挤压的贷款（库存*单价）

   select goods_id,goods_name,shop_price,shop_price*goods_number from goods;
   

   这里不用对 where 查询出来的内容做筛选

2. 查询该店积压的总货款（每种商品挤压的货款值和）：

   select sum(shop_price*goods_number) from goods;
   

   sum() 中填写表达式即可

3. 查询每个栏目下积压的货款：

   select cat_id,sum(shop_price*goods_number) from goods group by cat_id;
   

   同样的，这里不需要对结果做筛选，只是分组，所以使用 group by 即可

4. 查询积压超过 20000 元的栏目，以及该栏目积压的货款：

   select cat_id,sum(shop_price*goods_number) as zhk from goods group by cat_id having zhk > 20000;
   

练习

1. 现有如下表：

having 查询练习表 1.jpg

要求：查询出 2 门及 2 门以上不及格者的平均成绩：

方案一：

select name,avg(score) from scores where score < 60 group by name having count(*) >= 2;

结果如图：

having 查询练习1-方案1结果.jpg

这种解法有个地方被忽略了，张三的数学是大于 90 的，而这种解法将 score<60 的判断放在 where 后面，导致只能统计出低于 60 部分的平均分

方案二：

select name,avg(score),count(score < 60) as gks from scores group by name having gks >= 2;

这种解法就是在上一种上面略作改进，既然 score < 60 的判断不能放在 where 后面，那就放在 where 前面。在原表的基础上插入以下数据：

insert into scores values ('赵六','语文',100),('赵六','数学',99),('赵六','政治',98);

然后再计算发现结果如下：

having 查询练习1-方案2结果.jpg

可以发现，即使赵六成绩都及格，但是也被统计出来了。因为 count() 在计数的时候，无论里面的内容是真是假都被计数。

方案三：

首先将题目分解，如果是求所有人的平均分，那么语句为：

select name,avg(score) from scores group by name;

如果要计算出每个人的挂科情况：

select name,subject,avg(score),score<60 as g from scores;

这样的话所有小于 60 的科目 g 都是 1 ，大于等于 60 的为 0。

那么挂科的数量就是 sum(g)：

select name,avg(score),sum(score<60) as gks from scores group by name; 

最后再用 having 筛选就可以了：

select name,avg(score),sum(score<60) as gks from scores group by name having gks >= 2;

这其实就是把方案二的 count() 换成了 sum()

还可以用子查询的方式，但是相对麻烦不少，如下：

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