查询本店价比市场价所省的钱。并且要求省钱200元以上
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
where 1
having discount > 200;
查询每种商品积压的货款。
商品*价格
select goods_id,shop_price,goods_number,shop_price*goods_number
as huokuan from goods;
查询该店积压的总贷款
每个商品积压的货款之和
select sum(goods_number*shop_price) from goods;
查询每个栏目下积压的货款
select cat_id, sum(goods_number*shop_price) from goods group by cat_id;
查询出栏目积压货款金额。且要求查处金额大于两万的。
用到了group 与having
select cat_id,sum(goods_number*shop_price) as zhk from goods group by cat_id having zhk>20000;
查询本店价比市场价省的钱而且筛选出省钱200以上的商品。
用where having分别实现。
where实现
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
where (market_price-shop_price) >200;
having实现
select goods_id,market_price,shop_price,(market_price-shop_price) as discount
from goods
having discount >200;
mysql> create table result (
-> name varchar(20),
-> subject varchar(20),
-> score tinyint
-> )engine myisam charset utf8;
insert into result values
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);
查询出两门及两门以上不及格者的平均成绩。
第一步：查询多有的平均分
select name,avg(score) from result group by name;
下一步，算出每个人挂科的情况
select name,subject,score,score<60 as g feom result
综合上面两个语句
select name,avg(score),sum(score<60) as gks
from result group by name having gks>=2;
order by
当最终结果集出来，可以进行排序。
取出四个栏目下的商品，并且按价格由高到低排序。
select goods_id,goods_name,shop_price
from goods
where cat_id = 4
order by shop_price desc;
降序排列。
排序的语法：
order by 结果集中的列名desc/asc
按发布的时间升序排列即发布早的时间戳小的，靠前
select goods_id,goods_name,shop_price
from goods
where cat_id = 4
order by add_time asc;
按栏目排序
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc;
我们按栏目升序排列，同一个栏目下的商品，再按商品的价格降序排列
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc,shop_price desc;
多字段排序
order by 列 desc/asc，列2 desc/asc，列3 desc/acs
限制条目
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id=3
order by shop_price asc
limit 10;
查询出本店价格最高的前三名
Select goods_id,goods_name,shop_price
from goods
order by shop_price
desc
limit 0,3;
查询出本店最高的第三名和第五名
取第三到第五，即意味着跳过第一第二。因此偏移量offset是2
取第3 4 5条，即取三条，即N=3
select goods_id,goods_name,shop_price
from goods
order by shop_price desc
limit 2,3;
取出价格最高的一条商品
select goods_id,goods_name,shop_price
from goods
order by shop_price desc
limit 0,1;