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综合练习题
有如下表及数据
|
1 2 3 4 5 6 7 8 9 10 |
+------+---------+-------+ | name | subject | score | +------+---------+-------+ | 张三 | 数学 | 90 | | 张三 | 语文 | 50 | | 张三 | 地理 | 40 | | 李四 | 语文 | 55 | | 李四 | 政治 | 45 | | 王五 | 政治 | 30 | +------+---------+-------+ |
要求:查询出2门及2门以上不及格者的平均成绩
-----------------------------------------------------------------------------
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
## 一种错误做法 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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
#正确思路,先查看每个人的平均成绩 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) |
2016年6月7日 下午6:05 1F
非常适合我们初学者…