#having详解
(一)#查询出本店价格比市场价格低多少钱,并且把低200元以上的选出来
#先做前半部分select goods_id,goods_name,market_price - shop_price as sheng from goods;
#再做后半部分
1.先来个错误的示例:
select goods_id,goods-name,market_price - shop_price as sheng from goods where sheng>200;
2.#正确示例:
1)要是不用having,也可以正确查询,如下:
select goods_id,goods_name,market_price -shop_price as sheng goods where market_price -shop_price>200;
2)#把where替换掉having,来简化查询:
select goods_id,goods_name,market_price -shop_price as sheng from goods having sheng >200;
(二)#同上题,只不过查第3个栏目下比市场价低于200以上的商品
slect goods_is,cat_id,market_price -shop_price as sheng from goods where cat_id =3 having sheng > 200;1.#查询积压货款超过2w的栏目,以及该栏目积压的货款;
1)#先做后半句select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id;
2)#再筛选,再用having完成前半句;
select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id having hk >20000;
#
#查询两门及两门以上不及格同学的平均分(及格的课程也得算上)
#要求只见一次select完成,
1)计算平均分:
select name,avg(score) from table_name group by name;
2)每个人的挂科科目:
slect name,score<60 from stu;
3)统计每个的挂科数目
select name,sum(score<60) from stu group by name;
4) 在3)添加挂科数目大于等于2,并且平均分小于60:
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk>=2;