MySQL查询语句（四）——order by与limit

MySQL查询语句（三）——having筛选

order by

order by对最终的结果集进行排序，放在语句where/group by/having后面
1、使用商品价格对搜索结果进行降序排列desc（升序排列asc ）

mysql> select goods_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc;

2、先按照栏目排序再按照商品价格排序

mysql> select goods_id,goods_name,shop_price from goods where cat_id=4 order by cat_id asc,shop_price desc;

limit

限制显示的条目limit [n],m
n：（可选）偏移量，跳过几行
m：取出条目
1、取出前10行和3到5行

mysql> select goods_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc limit 10;
mysql> select goods_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc limit 2,3;

2、取出每个栏目价格最高的
需要使用子查询

mysql> select cat_id,shop_price from (select cat_id,goods_name,shop_price from goods order by cat_id,shop_price desc)  as tmp group by cat_id;

MySQL查询语句（五）——子查询