转载

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        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

正文到此结束