hive select查询语句

hive  select查询语句

现在不想整理了！！！！！！！！！有空再整理了！！！

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[LIMIT number]


eg:
select empno, count from db_hive_emp limit 5 ;
limit:
从查询分析的结果集中显示多少条数据。


>>>>>>>>
全表查询、指定字段查询
use db_hive_0927 ;
select * from emp ;   -- 不跑mr任务
select empno, ename, deptno from emp ;   --执行mr任务


>>>>>>>>
= />= / <= /between and /limit
select empno, ename, deptno from emp where empno >= 7782 ;
select * from emp limit 5 ;
查询emp中工资在800 到 1500 之间的人
select ename, sal from emp where sal between 800 and 1500 ;


>>>>>>>>>>
(not) in / is (not) null
select ename, sal, comm from emp where comm is null ;
select ename, sal, comm from emp where comm is not null ;


>>>>>>>>>
max/min/count/sum/avg 
select count(*) from emp ;
select count(1) from emp ;
select max(sal) max_sal from emp ;
select avg(sal) avg_sal from emp ;


>>>>>>>>>
group by / having
select deptno ,count(1) cnt from emp group by deptno ;


select deptno ,avg(sal) cnt from emp group by deptno ;


having 是对分组结果进行筛选的
select deptno ,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000 ;