多行子查询-big.data.analytics.with.spark

7.1、单行子查询select * from emp where sal > (select sal from emp where empno = 7566); 7.2、子查询空值/多值问题如果子查询未返回任何行，则主查询也不会返回任何结果(空值)select * from emp where sal > (select sal from emp where empno = 8888);如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符(正常)select * from emp where sal > (select sal from emp where empno = 7566);如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法7.3、多行子查询select * from emp where sal > any(select avg(sal) from emp group by deptno); select * from emp where sal > all(select avg(sal) from emp group by deptno); select * from emp where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH'); 7.4、TopN查询select * from emp where rownum=1 or rownum=2; select *