HiveQL Select-Order By

Hive Select-Order By

本章说明如何在SELECT语句中使用ORDER BY子句。ORDER BY子句用于根据一列检索详细信息，并按升序或降序对结果集进行排序。

句法

下面给出的是ORDER BY子句的语法：


SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[HAVING having_condition] 
[ORDER BY col_list]] 
[LIMIT number];

例 - 让我们以SELECT ... ORDER BY子句为例。假定员工表如下所示，其字段名为Id，Name，Salary，Designation和Dept。使用Department name生成查询以按顺序检索员工详细信息。


+------+--------------+-------------+-------------------+--------+
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+
|1201  | Gopal        | 45000       | Technical manager | TP     |
|1202  | Manisha      | 45000       | Proofreader       | PR     |
|1203  | Masthanvali  | 40000       | Technical writer  | TP     |
|1204  | Krian        | 40000       | Hr Admin          | HR     |
|1205  | Kranthi      | 30000       | Op Admin          | Admin  |
+------+--------------+-------------+-------------------+--------+

以下查询使用上述场景检索员工详细信息：


hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;

成功执行查询后，您将看到以下响应：


+------+--------------+-------------+-------------------+--------+
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+
|1205  | Kranthi      | 30000       | Op Admin          | Admin  |
|1204  | Krian        | 40000       | Hr Admin          | HR     |
|1202  | Manisha      | 45000       | Proofreader       | PR     |
|1201  | Gopal        | 45000       | Technical manager | TP     |
|1203  | Masthanvali  | 40000       | Technical writer  | TP     |
+------+--------------+-------------+-------------------+--------+

使用JDBC程序

这是在给定示例中应用Order By子句的JDBC程序。


import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLOrderBy {
   private static String driverName = "org.apache.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
      try {
          // Register driver and create driver instance
         Class.forName(driverName);
         
         // get connection
         Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/userdb", "", "");
         
         // create statement 
         Statement stmt = con.createStatement();
         
         // execute statement
         Resultset res = stmt.executeQuery("SELECT * FROM employee ORDER BY DEPT");
         System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");
         
         while (res.next()) {
            System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));
         }
         
         con.close();
      } catch (Exception e) {
         System.out.println(e.getMessage());
      }     
   }
}

将程序保存在名为HiveQLOrderBy.java的文件中。使用以下命令来编译和执行该程序。


$ javac HiveQLOrderBy.java
$ java HiveQLOrderBy

输出：


ID       Name           Salary      Designation          Dept
1205     Kranthi        30000       Op Admin             Admin
1204     Krian          40000       Hr Admin             HR
1202     Manisha        45000       Proofreader          PR
1201     Gopal          45000       Technical manager    TP
1203     Masthanvali    40000       Technical writer     TP
1204     Krian          40000       Hr Admin             HR

HiveQL Select-Order By

Hive Select-Order By

句法