MySQL查询语句（三）——having筛选

MySQL查询语句（二）——group by分组及统计函数

having可以对结果再次进行筛选（where只对表格原有数据进行查询）
1、查出本店比商场价省200以上的商品

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

2、查出每种商品积压的货款，商品*价格

mysql> select goods_id,shop_price,(shop_price*goods_number) as h from goods;

3、查出积压的总货款

mysql> select sum(shop_price*goods_number) from goods;

4、查出每个栏目下积压的货款

mysql> select sum(shop_price*goods_number) from goods group by cat_id；

5、查出栏目积压货款金额大于30000的

mysql> select cat_id,sum(shop_price*goods_number) as q from goods group by cat_id having q>30000;

6、查出下表2门及2门以上成绩不及格者的平均成绩（难度较大的综合题）

mysql> create table result(
    -> name varchar(20) not null default '',
    -> subect varchar(20) not null default '',
    -> score smallint(5) not null default '0'
    -> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into result values ('张三','数学',120), ('张三','语文',12), ('张三','地理',20), ('李四','数学',44), ('李四','语文',33), ('王五','数学',5);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from result;
+--------+--------+-------+
| name   | subect | score |
+--------+--------+-------+
| 张三   | 数学   |   120 |
| 张三   | 语文   |    12 |
| 张三   | 地理   |    20 |
| 李四   | 数学   |    44 |
| 李四   | 语文   |    33 |
| 王五   | 数学   |     5 |
+--------+--------+-------+
6 rows in set (0.00 sec)

#方法一
mysql> select name,avg(score),sum(score<60) as c from result group by name having c>=2;
+--------+------------+------+
| name   | avg(score) | c    |
+--------+------------+------+
| 张三   |    50.6667 |    2 |
| 李四   |    38.5000 |    2 |
+--------+------------+------+
2 rows in set (0.00 sec)


#方法二（直接嵌套子查询）
#先找出挂科数不小于2的name
mysql> select name,count(1) as gks from result where score<60 group by name having gks>=2;
+--------+-----+
| name   | gks |
+--------+-----+
| 张三   |   2 |
| 李四   |   2 |
+--------+-----+
2 rows in set (0.00 sec)

mysql> select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as tmp;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

#把这个结果里面的name当成where的输入
mysql> select name,avg(score) from result where name in ('张三','李四') group by name;
+--------+------------+
| name   | avg(score) |
+--------+------------+
| 张三   |    50.6667 |
| 李四   |    38.5000 |
+--------+------------+
2 rows in set (0.00 sec)

mysql> 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;
+--------+------------+
| name   | avg(score) |
+--------+------------+
| 张三   |    50.6667 |
| 李四   |    38.5000 |
+--------+------------+
2 rows in set (0.00 sec)

常见错误1
只计算挂科成绩的平均分

mysql> select name,avg(score),count(score) as c from result where score<60 group by name having c>=2;
+--------+------------+---+
| name   | avg(score) | c |
+--------+------------+---+
| 张三   |    16.0000 | 2 |
| 李四   |    38.5000 | 2 |
+--------+------------+---+
2 rows in set (0.00 sec)

常见错误2
计算科目总数大于2的，挂科数不对

mysql> select name,avg(score),count(score<60) as c from result  group by name having c>=2;
+--------+------------+---+
| name   | avg(score) | c |
+--------+------------+---+
| 张三   |    50.6667 | 3 |
| 李四   |    38.5000 | 2 |
+--------+------------+---+
2 rows in set (0.00 sec)

MySQL查询语句（四）——order by与limit