mysql order by-mysql教程-PHP中文网

mysql order by

黄舟

发布： 2017-01-16 13:09:00

原创

1448人浏览过

order by
排序：在结果集出来之后才有意义必须在where ,group by ，having 之后
desc（降序）/asc(升序)

用字段排序

用shop_price 降序排列

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

登录后复制

多个排序选择，先根据cat_id,然后shop_price

select cat_id,shop_price,goods_name from goods order by cat_id ,shop_price;

登录后复制

limit限制 limit [pos, 可选偏移量] 数量

升序排列取出前十名

select goods_id,goods_name from goods where cat_id=3 order by shop_price asc limit 10;

登录后复制

价格最高的前五名

mysql> select goods_name ,shop_price from goods order by shop_price desc limit 0,5;

登录后复制

mysql> select goods_name ,shop_price from goods order by shop_price desc limit 5;
+----------------+------------+
| goods_name | shop_price |
+----------------+------------+
| 多普达Touch HD | 5999.00 | 
| 诺基亚N96 | 3700.00 | 
| 诺基亚N85 | 3010.00 | 
| testPhone | 3000.00 | 
| 夏新T5 | 2878.00 | 
+----------------+------------+

登录后复制

价格最高的从第三名开始的三名（或者说是第三名到第五名）

mysql> select goods_name ,shop_price from goods order by shop_price desc limit 2,3;
+------------+------------+
| goods_name | shop_price |
+------------+------------+
| 诺基亚N85 | 3010.00 | 
| testPhone | 3000.00 | 
| 夏新T5 | 2878.00 | 
+------------+------------+

登录后复制

取出价格最高的商品

mysql> select goods_name ,shop_price from goods order by shop_price desc limit 1;
+----------------+------------+
| goods_name | shop_price |
+----------------+------------+
| 多普达Touch HD | 5999.00 | 
+----------------+------------+

登录后复制

技巧：[判断where] [分组group_by] [过滤having] [排序order by] [筛选limit]

取出每个类型中最新的产品

select cat_id, goods_id ,goods_name from(
(select cat_id,goods_id ,goods_name from goods order by cat_id desc,goods_id desc ) as tmp
)group by cat_id order by cat_id desc;

登录后复制

select cat_id,goods_id ,goods_name from goods where goods_id in (
select max(goods_id) from goods group by cat_id 
) order by cat_id desc;

登录后复制

查询出来的结果可以是
单列单行可以用= 再次过滤
单列多行可以用in 再次过滤
多列多行可以用from 再次过滤

以上就是mysql order by的内容，更多相关内容请关注PHP中文网（www.php.cn）！