Oracle学习札记4-稍复杂的查询和分页

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

最后一步看仔细了：