Sql 简明教程
SQL - ORDER BY Clause
The SQL ORDER BY Clause
SQL ORDER BY 从句用于根据一列或多列以升序或降序对数据进行排序。此从句可以通过单个列或多列来对数据进行排序。当需要对数据进行分层排序（如按州、城市，再按个人名称排序）时，按多列排序非常有用。
The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. This clause can sort data by a single column or by multiple columns. Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting by state, city, and then by the person’s name.
ORDER BY 与 SQL SELECT 语句一起使用，通常指定在 WHERE、HAVING 和 GROUP BY 从句之后。
ORDER BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses.
以下是有关 ORDER BY 从句的重要要点：
Following are the important points about ORDER BY Clause −
-
Some databases sort the query results in an ascending order by default.
-
To sort the data in ascending order, we use the keyword ASC.
-
To sort the data in descending order, we use the keyword DESC.
除了按照升序或降序对记录进行排序之外，ORDER BY 从句还可以按照首选顺序对数据库表中的数据进行排序。
In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table in a preferred order.
此首选顺序可能不会按任何标准顺序（如按字母顺序或音序）对表的记录进行排序，但它们可以根据外部条件进行排序。
This preferred order may not sort the records of a table in any standard order (like alphabetical or lexicographical), but they could be sorted based on external condition(s).
例如，在包含组织客户详细信息的 CUSTOMERS 表中，可以根据他们所在城市的居民来对记录进行排序。这无需按字母顺序进行排序，而是需要使用 CASE 语句手动定义顺序。
For instance, in the CUSTOMERS table containing the details of the customers of an organization, the records can be sorted based on the population of the cities they are from. This need not be alphabetically sorted, instead, we need to define the order manually using the CASE statement.
Syntax
ORDER BY 从句的基本语法如下：
The basic syntax of the ORDER BY clause is as follows −
SELECT column-list
FROM table_name
[ORDER BY column1, column2, .. columnN] [ASC | DESC];其中 column-list 是要检索的列列表；且 ASC 或 DESC 指定排序顺序。
Where, column-list is list of the columns we want to retrieve; and ASC or DESC specifies the sort order.
Note: 可以在 ORDER BY 从句中使用多列，但需要确保用于排序的列是在列列表中指定的。
Note: We can use more than one column in the ORDER BY clause, but we need to make sure that the column we are using to sort is specified in the column-list.
ORDER BY Clause with ASC
可以通过指定 ASC 作为排序顺序，使用 SQL ORDER BY 从句按升序（基于一列或多列）对查询的结果集进行排序。ASC 是此从句的默认排序顺序，即在使用 ORDER BY 从句时，如果您未明确指定排序顺序，数据将按升序进行排序。
We can sort the result-set of a query in ascending order (based on one or more columns) using the SQL ORDER BY clause by specifying ASC as the sort order. ASC is the default sort order for this clause, i.e. while using the ORDER BY clause if you do not explicitly specify the sort order, the data will be sorted in ascending order.
Example
假设使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示：
Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询使用 INSERT 语句将值插入此表：
Following query inserts values into this table using the INSERT statement −
insert INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);获得的表如下所示：
The table obtained is as shown below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
在以下查询中，正在根据 NAME 列以升序对 CUSTOMERS 表的记录进行排序：
In the following query, we are sorting the records of the CUSTOMERS table in ascending order based on the column NAME −
SELECT * FROM CUSTOMERS ORDER BY NAME ASC;ORDER BY Clause with DESC
要按降序（基于一列或多列）对查询的结果集进行排序，需要使用 ORDER BY 从句，并指定 DESC 作为排序顺序。
To sort the result-set of a query in descending order (based on one or more columns), we need to use the ORDER BY clause by specifying DESC as the sort order.
ORDER BY Clause on Multiple Columns
我们可以使用 ORDER BY 子句按多列（多于一列）对查询的结果集进行排序。按多列进行排序时，将会按 ORDER BY 子句中指定的顺序进行排序。换句话说，该表将按第一个列（在查询中指定）、第二个列等进行排序。
We can use the ORDER BY clause to sort the result-set of a query by multiple (more than one) columns. When sorting by multiple columns, the sorting is done in the order that is specified in the ORDER BY clause. In other words, the table will be sorted based on the first column (specified in the query), then the second column, and so on.
Example
在以下查询中，我们将检索 CUSTOMERS 表中的所有记录，首先按其地址升序对它们进行排序，然后按其工资降序对它们进行排序：
In the following query, we are retrieving all records from the CUSTOMERS table and sorting them first by their address in ascending order, and then by their salary in descending order −
SELECT * FROM CUSTOMERS ORDER BY AGE ASC, SALARY DESC;ORDER BY with WHERE Clause
我们还可以将 WHERE 子句与 ORDER BY 子句结合使用，以对满足某些条件的行进行排序。当我们想要基于特定条件对表中的数据子集进行排序时，这会非常有用。
We can also use the WHERE clause with the ORDER BY clause to sort the rows that meet certain conditions. This can be useful when we want to sort a subset of the data in a table based on the specific criteria.
ORDER BY with LIMIT Clause
我们可以将 LIMIT 子句与 ORDER BY 子句结合使用，通过按升序或降序对指定数量的行进行排序来限制这些行。
We can use the LIMIT clause with ORDER BY clause to limit the specified number of rows by sorting them either in ascending or in descending order.
Syntax
以下是 MySQL 数据库中使用 LIMIT 子句和 ORDER BY 子句的语法：
Following is the syntax of using the LIMIT clause with the ORDER BY clause in MySQL database −
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name1 [ASC | DESC], column_name2 [ASC | DESC], ...
LIMIT N;Sorting Results in a Preferred Order
还可以使用 ORDER BY 子句中的 CASE 语句按自己偏好的顺序对表的记录进行排序。所有值均指定在该子句中以及它们应该排序的位置；如果没有给值指定任何数字，则会自动按升序对它们进行排序。
One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order.
Example
要按自己的首选顺序提取行，将使用以下 SELECT 查询：
To fetch the rows with their own preferred order, the SELECT query used would be as follows −
SELECT * FROM CUSTOMERS ORDER BY (
CASE ADDRESS
WHEN 'MUMBAI' THEN 1
WHEN 'DELHI' THEN 2
WHEN 'HYDERABAD' THEN 3
WHEN 'AHMEDABAD' THEN 4
WHEN 'INDORE' THEN 5
WHEN 'BHOPAL' THEN 6
WHEN 'KOTA' THEN 7
ELSE 100 END
);Output
上面的查询基于使用 CASE 语句定义的自定义顺序对 CUSTOMERS 表进行排序。在这里，我们将基于 ADDRESS 列中指定的城市人口对记录进行排序。
The above query sorts the CUSTOMERS table based on the custom order defined using the CASE statement. Here, we are sorting the records based on the population of the cities specified in the ADDRESS column.
ID |
NAME |
AGE |
ADDRESS |
SALARY |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |