大数据HiveQL 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 的字段，生成一个查询用于检索员工的详细信息。

  +------+--------------+-------------+-------------------+--------+  | 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 程序

下面是JDBC程序应用给定Order By子句的例子。

  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.hadoop.hive.jdbc.HiveDriver";          public static void main(String[] args) throws SQLException {             // Register driver and create driver instance        Class.forName(driverName);                // get connection        Connection con = DriverManager.getConnection("jdbc:hive://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();     }  }

保存程序在一个名为HiveQLOrderBy.java文件。使用下面的命令来编译并执行这个程序。

  $ javac HiveQLOrderBy.java  $ java HiveQLOrderBy

输出

  ID       Name           Salary      Designation          Dept  1205     Kranthi   

推荐学习目录：Hive视图和索引