mysql基础④group by分组与统计函数及having运用

样表如②

一.group by分组与统计函数；

1.查询最贵的商品价格

select goods_id,goods_name,cat_id,max(shop_price) from goods;

2.查出最大最新的商品编号

select max(goods_id),goods_name,cat_id from goods;

3.查出最便宜的商品价格

select goods_id,goods_name,min(shop_price) from goods;

4.查出最旧最小的商品编号

select min(goods_id),goods_name,shop_price from goods;

5.查出该店所有商品的的库存总量

select goods_id,goods_name,sum(goods_number) from goods;

6.查询出所有商品的平均价格

select goods_id,goods_name,avg(shop_price) from goods;

7.查询出该店一共有多少种商品

select count(*) from goods;

8.查询每个栏目下面最贵的商品

select goods_id,goods_name,max(shop_price) from goods group by cat_id;

9.查询出每个栏目下最低的商品价格

select goods_id.goods_name,min(shop_price) from goods group by cat_id;

10.查询每个栏目下商品平均价格

select goods_id,goods_name,avg(shop_price) from goods group by cat_id;

11.查询每个栏目下商品的库存量

select goods_id,goods_name,sum(goods_number) from goods group by cat_id;

12.查询每个栏目下的商品种类

select cat_id,count(*) from goods group by cat_id;

 

二、where与group及having综合运用查询

1.查询该店的商品比市场价所节省的价格

select goods_id,goods_name,cat_id,(market_price-shop_price) as sheng from goods;

2.查询每个商品所积压的货款

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

3.查询该店积压的总货款

select sum(shop_price*goods_number) from goods;

4.查询每个栏目积压的总货款

select cat_id,sun(shop_price*goods_number) as jiya from goods group by cat_id;

5.查询比市场价省钱200元以上的商品及该商品所省的钱（where和having分别实现）

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

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

6.查询积压货款超过2W的栏目，以及该栏目积压的货款

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

思路：首先查询出每个人的平均成绩

select name avg(score) from stu group by name;

同时计算每个人的挂科科目

select name,sum(score<60),avg(score) as pingjunfeng from stu group by name;

利用having筛选挂科二门以上的

select name,sum(score<60) as guakeshu,avg(score) as pingjungfeng from stu group by name having guakeshu>=2;