求知 文章 文库 Lib 视频 iPerson 课程 认证 咨询 工具 讲座 Model Center   Code  
会员   
要资料
 
 
 

Hive教程
Hive安装
Hive数据类型
Hive创建数据库
Hive删除数据库
Hive创建表
Hive修改表
Hive删除表
Hive分区
Hive内置运算符
Hive内置函数
Hive视图和索引
HiveQL Select Where
HiveQL Select Order By
HiveQL Select Group By
HiveQL Select Join
 
 

HiveQL Select Order By
875 次浏览
32次  

本章介绍了如何使用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 

 


您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码: 验证码,看不清楚?请点击刷新验证码 必填



875 次浏览
32次
 捐助