SQL ORDER BY子句:排序

 
SQL ORDER BY 子句用于根据一个或者多个字段对查询结果(结果集)进行排序,可以是降序,也可以是升序。默认情况下,大部分数据库将查询结果按照升序排序。

语法

ORDER BY 子句的基本语法如下所示:

SELECT column_list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

您可以在 ORDER BY 子句中指定多个用于排序的字段,它们之间以逗号,分隔;但是,您应该确保这些字段都位于 column_list 中。

ASC 关键字表示升序,DESC 关键字表示降序;如果不写,大部分数据库默认为 ASC。

示例

现在有一个包含如下记录的 website 表:
+----+----------------+----------------------------+-----+-------+---------+---------+
| id | name           | url                        | age | alexa | uv      | country |
+----+----------------+----------------------------+-----+-------+---------+---------+
|  1 | 百度           | https://www.baidu.com/     |  21 |     4 |  5010.5 | CN      |
|  2 | 淘宝           | https://www.taobao.com/    |  17 |     8 | 3996.75 | CN      |
|  3 | C语言中文网    | http://c.biancheng.net/    |  12 |  7923 |   11.62 | CN      |
|  4 | Google         | https://www.google.com/    |  23 |     1 |   36474 | US      |
|  5 | GitHub         | https://github.com/        |  13 |    95 |   216.3 | US      |
|  6 | Stack Overflow | https://stackoverflow.com/ |  16 |    48 |   592.2 | US      |
|  7 | Yandex         | http://www.yandex.ru/      |  11 |    53 |  591.82 | RU      |
|  8 | VK             | https://vk.com/            |  23 |    23 |    1206 | RU      |
+----+----------------+----------------------------+-----+-------+---------+---------+
下面的 SQL 语句将根据 age 和 uv 字段对结果集进行升序排序:
SELECT * FROM website
ORDER BY age, uv;
执行结果:
+----+----------------+----------------------------+-----+-------+---------+---------+
| id | name           | url                        | age | alexa | uv      | country |
+----+----------------+----------------------------+-----+-------+---------+---------+
|  7 | Yandex         | http://www.yandex.ru/      |  11 |    53 |  591.82 | RU      |
|  3 | C语言中文网    | http://c.biancheng.net/    |  12 |  7923 |   11.62 | CN      |
|  5 | GitHub         | https://github.com/        |  13 |    95 |   216.3 | US      |
|  6 | Stack Overflow | https://stackoverflow.com/ |  16 |    48 |   592.2 | US      |
|  2 | 淘宝           | https://www.taobao.com/    |  17 |     8 | 3996.75 | CN      |
|  1 | 百度           | https://www.baidu.com/     |  21 |     4 |  5010.5 | CN      |
|  8 | VK             | https://vk.com/            |  23 |    23 |    1206 | RU      |
|  4 | Google         | https://www.google.com/    |  23 |     1 |   36474 | US      |
+----+----------------+----------------------------+-----+-------+---------+---------+

再如,下面的 SQL 语句将根据 alexa 字段对结果集进行降序排序:
SELECT * FROM website
ORDER BY alexa DESC;
执行结果:
+----+----------------+----------------------------+-----+-------+---------+---------+
| id | name           | url                        | age | alexa | uv      | country |
+----+----------------+----------------------------+-----+-------+---------+---------+
|  3 | C语言中文网    | http://c.biancheng.net/    |  12 |  7923 |   11.62 | CN      |
|  5 | GitHub         | https://github.com/        |  13 |    95 |   216.3 | US      |
|  7 | Yandex         | http://www.yandex.ru/      |  11 |    53 |  591.82 | RU      |
|  6 | Stack Overflow | https://stackoverflow.com/ |  16 |    48 |   592.2 | US      |
|  8 | VK             | https://vk.com/            |  23 |    23 |    1206 | RU      |
|  2 | 淘宝           | https://www.taobao.com/    |  17 |     8 | 3996.75 | CN      |
|  1 | 百度           | https://www.baidu.com/     |  21 |     4 |  5010.5 | CN      |
|  4 | Google         | https://www.google.com/    |  23 |     1 |   36474 | US      |
+----+----------------+----------------------------+-----+-------+---------+---------+