本章介绍如何在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 | +------+--------------+-------------+-------------------+--------+
这是给定示例应用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.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