oracle查询笔记(2)

11.在多行子查询中使用all操作符
all表示所有，用在子句中
select ename,sal from emp where sal>all(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
12.多行子查询中使用any操作符
select ename,sal from emp where sal>any(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select min(sal) from emp where deptno=30);
13.多列子查询
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
14.oracle分页
1)rownum分页
  select * from
  (select rownum rn,a1.* from emp a1 where rownum<9)
  where rn>4;
  注意：所有的改动都只改里面的查询
2)rowid分页(效率是rownum的3倍)
  select * from emp where rowid in
  (select rid from
  (select rownum rn,rid from
  (select rowid rid,empno from emp order by empno desc)
  where rownum<10)
  where rn>3)
  order by empno desc;
15.如何显示高于自己部门平均工资的员工的信息
select a1.* from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.avgsal;
16.用查询结果创建新表
create table emp2 (empno,ename,sal) as
select empno,ename,sal from emp;