having与group综合运用查询

  • 1,352

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)

 

weinxin
我的微信
这是我的微信扫一扫
开拓者博主
  • 本文由 发表于 2016年6月3日22:45:02
  • 转载请务必保留本文链接:https://www.150643.com/60.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

评论:1   其中:访客  1   博主  0
    • 深圳seo 深圳seo 0

      非常适合我们初学者…