Impala - ORDER BY 子句

Impala 的ORDER BY子句用於根據一個或多個列對資料進行升序或降序排序。某些資料庫預設按升序對查詢結果進行排序。

語法

以下是 ORDER BY 子句的語法。

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

您可以使用關鍵字ASC或DESC分別將表中的資料排列為升序或降序。

同樣，如果我們使用 NULLS FIRST，則表中的所有空值都排列在頂部行；如果我們使用 NULLS LAST，則包含空值的行列將排列在最後。

示例

假設我們在資料庫my_db中有一個名為customers的表，其內容如下所示：

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 1  | Ramesh   |  32 | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 4  | Chaitali | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.51s

以下是如何使用order by子句按id的升序排列customers表中的資料的示例。

[quickstart.cloudera:21000] > Select * from customers ORDER BY id asc;

執行上述查詢後，將產生以下輸出。

Query: select * from customers ORDER BY id asc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitali | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.56s

同樣，您可以使用如下所示的order by子句將customers表中的資料按降序排列。

[quickstart.cloudera:21000] > Select * from customers ORDER BY id desc;

執行上述查詢後，將產生以下輸出。

Query: select * from customers ORDER BY id desc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 6  | Komal    | 22  | MP        | 32000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 4  | Chaitali | 25  | Mumbai    | 35000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.54s