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综合练习题

有如下表及数据

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

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

 

weinxin
我的微信
把最实用的经验,分享给最需要的读者,希望每一位来访的朋友都能有所收获!
avatar
小鸟云服务器

发表评论

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

目前评论:1   其中:访客  1   博主  0

    • avatar 深圳seo 来自天朝的朋友 搜狗浏览器 Windows 7 山东省青岛市 鹏博士长城宽带 0

      非常适合我们初学者…