本文介绍如何使用ORDER BY
子句对查询结果进行排序。
语法
ORDER BY expression
[ ASC | DESC ]
[ LIMIT count]
参数 | 是否必填 | 说明 |
---|---|---|
expression |
必填 | 指定需要进行排序的字段,取值范围如下:
|
[ ASC | DESC ] |
选填 | 指定查询结果根据目标列进行升序或降序排列,取值范围如下:
说明
|
[ LIMIT count] |
指定查询结果的返回行数。
未指定该参数时,默认返回所有结果行。 |
示例
- 统计在不同城市售出的设备数量,并按照设备名称和销售量排列,语句如下:
SELECT os ,device,city,COUNT(*) AS num FROM requests GROUP BY os,device,city ORDER BY num,device;
返回结果如下:os |device |city |num --------+-------+-------------+--- windows |PC |Hangzhou |1 windows |PC |Shenzhen |1 windows |PC |Shanghai |1 linux |PC |Shanghai |1 windows |PC |Shijiazhuang |2 linux |PC |Beijing |2 windows |PC |Beijing |4 linux |Phone |Hangzhou |1 ios |Phone |Zhangjiakou |1 windows |Phone |Shenzhen |1 linux |Phone |Beijing |2 ios |Phone |Shijiazhuang |2 windows |Phone |Shijiazhuang |2
- 统计设备销售数量排名前5的城市,并按照设备名称降序,按照销售量升序排列,语句如下:
SELECT os,device,city,COUNT(*) AS num FROM requests GROUP BY os,device,city ORDER BY 2 DESC,4 ASC LIMIT 5;
返回结果如下:os |device |city |num -------+-------+------------+---- windows|Phone |Shenzhen |1 linux |Phone |Hangzhou |1 ios |Phone |Zhangjiakou |1 linux |Phone |Beijing |2 ios |Phone |Shijiazhuang|2