Sql 简明教程

SQL - SORTING Results

SQL ORDER BY 子句用于基于一列或多列以升序或降序对数据进行排序。默认情况下，一些数据库按升序对查询结果进行排序。

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. By default, some databases sort the query results in an ascending order.

此外，ORDER BY 子句还可以按首选顺序对数据库表中的数据进行排序。在这种情况下，可能不会按任何标准顺序（如按字母顺序或按字典顺序）对表的记录进行排序，但它们可以基于任何外部条件进行排序。例如，在包含由组织的多个客户下的订单列表的 ORDERS 表中，可以基于下订单的日期对下订单的详细信息进行排序。这不必按字母顺序排序，而应该基于“先到先得”。

In addition to that, ORDER BY clause can also sort the data in a database table in a preferred order. This case may not sort the records of a table in any standard order (like alphabetical or lexicographical), but, they could be sorted based on any external condition. For instance, in an ORDERS table containing the list of orders made by various customers of an organization, the details of orders placed can be sorted based on the dates on which those orders are made. This need not be alphabetically sorted, instead, it is based on "first come first serve".

Syntax

将用于按升序或降序对结果进行排序的 ORDER BY 子句的基本语法如下：

The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows −

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

你可以在 ORDER BY 子句中使用多列。确保你用于排序的任何列都应位于列列表中。

You can use more than one column in the ORDER BY clause. Make sure that whatever column you are using to sort, that column should be in the column-list.

Sorting Results in Ascending Order

使用 SQL 中的 Order By 子句，可以按升序或基于默认或通过在子句条件中指定“ASC”关键字对数据库表中的记录进行排序。让我们看一个示例来理解这一点。

Using Order By Clause in SQL, the records in a database table can be sorted in ascending order, either by default or by specifying the "ASC" keyword in the clause condition. Let us see an example to understand this.

Example

假设已使用如下所示的 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表：

Assume we have created a table named CUSTOMERS using the 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 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 will be created as −

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 和 SALARY 升序对结果进行排序的示例。

Following is an example, which would sort the result in an ascending order by NAME and SALARY.

SELECT * FROM CUSTOMERS ORDER BY NAME;

Output

这将产生以下结果 -

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
3	kaushik	23	Kota	2000.00
2	Khilan	25	Delhi	1500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00
1	Ramesh	32	Ahmedabad	2000.00

Sorting Results in Descending Order

但是，要按降序对数据库表中的记录进行排序，我们需要在子句条件中指定“DESC”关键字。让我们看一个示例来理解这一点。

But, to sort the records in a database table in descending order, we need to specify the "DESC" keyword in the clause condition. Let us see an example to understand this.

Example

以下查询按 NAME 列对 CUSTOMERS 表的记录按降序进行排序。

The following query sorts the records of the CUSTOMERS tables in descending order based on the column NAME.

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

这将产生以下结果 -

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
7	Muffy	24	Indore	10000.00
6	Komal	22	Hyderabad	4500.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00

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 'DELHI' 	 THEN 1
   WHEN 'BHOPAL' 	 THEN 2
   WHEN 'KOTA' 	 THEN 3
   WHEN 'AHMEDABAD' THEN 4
   WHEN 'Hyderabad' 	THEN 5
   ELSE 100 END) ASC, ADDRESS DESC;

Output

这将产生以下结果 -

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
3	kaushik	23	Kota	2000.00
1	Ramesh	32	Ahmedabad	2000.00
6	Komal	22	Hyderabad	4500.00
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

这会按你 own order 中的首选顺序先按 ADDRESS 对客户进行排序，并按自然顺序对剩余地址进行排序。此外，剩余的地址将按反向字母顺序进行排序。

This will sort the customers by ADDRESS in your own order of preference first, and in a natural order for the remaining addresses. Also, the remaining Addresses will be sorted in the reverse alphabetical order.