HiveQL Select Order By

广告位

本章介绍了如何使用SELECT语句的ORDER BY子句。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

关于作者: 大数据教程学习

为您推荐