在Mysql中做SQL语句:列出薪金比“SMITH”多的所有员工。
select ename,sal
from emp
where sal >
(
select sal from
emp where ename='SMITH'
);
但是在Hive中不支持where 在where 后面接 >子查询
where sal > (select .....)
在Hive中语法上不支持的,Hive上如何解决这样的HQL
采用将两表关联的做法,然后比较大小
select e1.ename,e1.sal from
(select ename,sal,1 as eid from emp) as e1
left join
(select 1 as did,sal from emp where ename="SMITH") as e2
on e1.eid=e2.did where e.sal>e2.sal;
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250