Oracle学习笔记4--稍复杂的查询和分页
仍然使用SCOTT用户来操作：
Select * from emp where sal>all(select sal from emp where deptno=30);
Select * from emp where sal>any(select sal from emp where deptno=30);
其实，换种方法也是可以的：
Select * from emp where sal>(select min(sal) from emp where deptno=30);
Select * from emp where (deptno,job)=(Select deptno,job from emp where ename=’SMITH’);
解决这个复杂一点的查询，我们可以这样考虑：
先找出每个部门的平均工资：
Select deptno,avg(sal) avg_sal from emp group by deptno;结果如下：
SQL> select deptno,avg(sal) avg_sal from emp group by deptno;
?
DEPTNO??? AVG_SAL
------ ----------
??? 30 1566.66666
??? 20?????? 2175
??? 10 2916.66666
然后我们就可以把这张表当成一张子表使用：
select a2.ename,a2.sal,a2.deptno,a1.avg_sal from emp a2,(select deptno,avg(sal) avg_sal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.avg_sal;
结果为：
ENAME??????????? SAL DEPTNO??? AVG_SAL
---------- --------- ------ ----------
ALLEN??????? 1600.00???? 30 1566.66666
JONES??????? 2975.00???? 20? ?????2175
BLAKE??????? 2850.00???? 30 1566.66666
SCOTT??????? 3000.00???? 20?????? 2175
KING???????? 5000.00???? 10 2916.66666
FORD???????? 3000.00???? 20?????? 2175
?
6 rows selected
上面这个例子，是在from子句中使用了子查询，这里说明一下：
当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当这样使用时一定要为子查询指定别名。
Sql server 2000中可以在别名前加as，Oracle中就打一空格就可以了，如果在Oracle中加了as是会出错的，这点注意。
Oracle中的分页较之DB2、SQL Server都不是很好做，总共有三种方法，这里就介绍一种：
先直接给出，再具体说下：
SQL> select * from(select a1.*,rownum rn from(select * from emp) a1 where rownum<10) where rn>=6;
?
EMPNO ENAME????? JOB???????? MGR HIREDATE????????? SAL????? COMM DEPTNO???????? RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
?7698 BLAKE????? MANAGER??? 7839 1981/5/1????? 2850.00?????????????? 30????????? 6
?7782 CLARK????? MANAGER??? 7839 1981/6/9????? 2450.00?????????????? 10????????? 7
?7788 SCOTT????? ANALYST??? 7566 1987/4/19???? 3000.00?????????????? 20????????? 8
?7839 KING?????? PRESIDENT?????? 1981/11/17??? 5000.00?????????????? 10????????? 9
注意有一列的属性是rn
首先是把select * from emp作为了一张字表a1，也就是这一部分：
(select * from emp) a1
然后为其加上行号：
Select a1.*,rownum rn from (select * from emp) a1
此时可以加一个where 子句限制一下条件：
Select a1.*,rownum rn from (select * from emp) a1 where rownum<10
这是取了前9行（rownum是从1而不是从0开始计数的）
如果要取第6行到第9行，可以在这个基础上再加条件，很多人可能会这么做：
Select a1.*,rownum rn from (select * from emp) a1 where rownum<10 and rownum>=6;
很遗憾，Oracle不是这么设计的……而是把以上结果再作为一个子查询使用：
Select * from(Select a1.*,rownum rn from (select * from emp) a1 where rownum<10 and rownum>=6)where rn>=6;
在做项目时，分页是很容易用到的，大家背也要背下来哦。
大家可以看到，我在上面做的查询是把emp表的所有字段都列了出来，而且没有排序，如果要做这些操作，只需要改最里边的子查询就可以了，也是蛮简单的……
还有另外两种方法我这也列出来，方便以后查看：
根据rowid 来分(这种方法效率最高)：
Select * from t_xiaoxi where rowid in (select rowid from(select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc)where rownum<10000)where rn>9980)order by cid desc;
按分析函数来分（这种方法效率最低）：
Select * from (select t.*,row_number() over (order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
推荐用最上面介绍的按照rownum来分的那种，虽然效率不是最高的，但是个人认为比较好用。当然，如果把分页用PLSQL写成一个函数或是过程，以后直接调用的话，也就无所谓了哈，呵呵~
补充：
其实分页这样写也是可以的：
select * from (select a1.*,rownum rn from (select * from emp order by sal) a1)where rn<=10 and rn>=6;
Create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
作为一个DBA，导表用这个比较快捷哦~
补充：
99年的标准：
1求部门中哪些人的薪水最高：
select ename,sal from emp join (select deptno,max(sal) max_sal from emp group by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
2求部门平均薪水的等级
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal );
3求部门薪水的平均等级
select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal)) t group by deptno;
一个优点变态的面试题：
不准用组函数，求薪水的最高值：
select ename,sal from emp where sal>=all(select sal from emp);
看看咱们考官心仪的答案：
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
?
求平均薪水最高的部门的部门编号：
select deptno,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal=( select max(avg_sal) from(select deptno,avg(sal) avg_sal from emp group by deptno));
求平均薪水最高的部门的部门名称：
select dname from dept where deptno=(
select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal=( select max(avg_sal) from(select deptno,avg(sal) avg_sal from emp group by deptno)));
下面来个巨复杂的：
求平均薪水的等级最低的部门的部门名称：
select dname,t1.deptno,grade,avg_sal from(
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal )
) t1
join dept on(t1.deptno=dept.deptno)
where t1.grade=
(
??? select min(grade) min_grade from(
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal ))
);
DNAME????????? DEPTNO????? GRADE??? AVG_SAL
-------------- ------ ---------- ----------
SALES????????????? 30????????? 3 1566.66666
分析一下：
先求平均薪水：
SQL> select deptno,avg(sal) avg_sal from emp group by deptno;
?
DEPTNO??? AVG_SAL
------ ----------
??? 30 1566.66666
??? 20?????? 2175
??? 10 2916.66666
接下来把求出的这张表作为一个子表和salgrade表做连接：
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal );
DEPTNO??? AVG_SAL????? GRADE
------ ---------- ----------
??? 10 2916.66666????????? 4
??? 20?????? 2175????????? 4
??? 30 1566.66666????????? 3
然后再从上面这张表中找出grade的最低值：
select min(grade) min_grade from(
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal ));
MIN_GRADE
----------
???????? 3
最后一步看仔细了：
select dname,t1.deptno,grade,avg_sal from(
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal )
) t1
join dept on(t1.deptno=dept.deptno)
where t1.grade=
(
??? select min(grade) min_grade from(
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) tt join salgrade s on(tt.avg_sal between s.losal and s.hisal ))
);
OK，搞定了……*_*……
上面我用红色标记出来的部分是完全一样的一个子查询，实际上为了简便，思路清晰起见，我们完全可以把这部分创建为一个视图使用，具体做起来比较简单我就不说了，注意一点，你的SCOTT用户如果不具有CREATE VIEW权限的话需要以管理员的权限赋予SCOTT用户该权限方能成功创建这个视图。
