having与group综合运用查询

3 having与group综合运用查询:

3.1:查询该店的商品比市场价所节省的价格

select goods_id,goods_name,market_price-shop_price as j

from ecs_goods ;

3.2:查询每个商品所积压的货款(提示:库存*单价)

select goods_id,goods_name,goods_number*shop_price from ecs_goods

3.3:查询该店积压的总货款

select sum(goods_number*shop_price) from ecs_goods;

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

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

-----------------------------------------------------------------------------

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

select goods_id,goods_name,market_price-shop_price as k from goods

where market_price-shop_price >200;

select goods_id,goods_name,market_price-shop_price as k from goods

having k >200;

-----------------------------------------------------------------------------

3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款

select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id

having k>20000

3.7:where-having-group综合练习题

有如下表及数据

　+------+---------+-------+
　　| name | subject | score |
　　+------+---------+-------+
　　| 张三 | 数学 | 90 |
　　| 张三 | 语文 | 50 |
　　| 张三 | 地理 | 40 |
　　| 李四 | 语文 | 55 |
　　| 李四 | 政治 | 45 |
　　| 王五 | 政治 | 30 |
　　+------+---------+-------+

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

-----------------------------------------------------------------------------

## 一种错误做法
　　mysql> select name,count(score=2;
　　+------+---+------------+
　　| name | k | avg(score) |
　　+------+---+------------+
　　| 张三 | 3 | 60.0000 |
　　| 李四 | 2 | 50.0000 |
　　+------+---+------------+
　　2 rows in set (0.00 sec)
　　mysql> select name,count(scoreselect name,count(score=2;
　　+------+---+------------+
　　| name | k | avg(score) |
　　+------+---+------------+
　　| 张三 | 3 | 60.0000 |
　　| 李四 | 2 | 50.0000 |
　　+------+---+------------+
　　2 rows in set (0.00 sec)
　　#加上赵六后错误暴露
　　mysql> insert into stu
　　-> values
　　-> ('赵六','A',100),
　　-> ('赵六','B',99),
　　-> ('赵六','C',98);
　　Query OK, 3 rows affected (0.05 sec)
　　Records: 3 Duplicates: 0 Warnings: 0
　　#错误显现
　　mysql> select name,count(score=2;
　　+------+---+------------+
　　| name | k | avg(score) |
　　+------+---+------------+
　　| 张三 | 3 | 60.0000 |
　　| 李四 | 2 | 50.0000 |
　　| 赵六 | 3 | 99.0000 |
　　+------+---+------------+
　　3 rows in set (0.00 sec)

#正确思路,先查看每个人的平均成绩
　　mysql> select name,avg(score) from stu group by name;
　　+------+------------+
　　| name | avg(score) |
　　+------+------------+
　　| 张三 | 60.0000 |
　　| 李四 | 50.0000 |
　　| 王五 | 30.0000 |
　　| 赵六 | 99.0000 |
　　+------+------------+
　　4 rows in set (0.00 sec)
　　mysql> # 看每个人挂科情况
　　mysql> select name,score < 60 from stu;
　　+------+------------+
　　| name | score < 60 |
　　+------+------------+
　　| 张三 | 0 |
　　| 张三 | 1 |
　　| 张三 | 1 |
　　| 李四 | 1 |
　　| 李四 | 1 |
　　| 王五 | 1 |
　　| 赵六 | 0 |
　　| 赵六 | 0 |
　　| 赵六 | 0 |
　　+------+------------+
　　9 rows in set (0.00 sec)
　　mysql> #计算每个人的挂科科目
　　mysql> select name,sum(score < 60) from stu group by name;
　　+------+-----------------+
　　| name | sum(score < 60) |
　　+------+-----------------+
　　| 张三 | 2 |
　　| 李四 | 2 |
　　| 王五 | 1 |
　　| 赵六 | 0 |
　　+------+-----------------+
　　4 rows in set (0.00 sec)
　　#同时计算每人的平均分
　　mysql> select name,sum(score < 60),avg(score) as pj from stu group by 
name;
　　+------+-----------------+---------+
　　| name | sum(score < 60) | pj |
　　+------+-----------------+---------+
　　| 张三 | 2 | 60.0000 |
　　| 李四 | 2 | 50.0000 |
　　| 王五 | 1 | 30.0000 |
　　| 赵六 | 0 | 99.0000 |
　　+------+-----------------+---------+
　　4 rows in set (0.00 sec)
　　#利用having筛选挂科2门以上的.
　　mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu 
group by name having gk >=2;
　　+------+------+---------+
　　| name | gk | pj |
　　+------+------+---------+
　　| 张三 | 2 | 60.0000 |
　　| 李四 | 2 | 50.0000 |
　　+------+------+---------+
　　2 rows in set (0.00 sec)