Oracle TOPN分析及 rownum相关知识
ORACLE中经常会进行TOP N的查询，即列出按照一定排序的数据记录。这种查询操作分两种：
1.排序，列出所有记录或前N条记录
2.排序，列出指定区间的记录
在ORACLE中是通过对表中的一个伪列 rownum进行操作的。也就是因为这一点，使得在ORACLE中进行TOP N的查询，尤其是指定区间的排序查询比较复杂和难以理解。
现在我们先看看第一种，根据第一种的定义我们很容易写出以下语句：
select 字段列表 from
(select 字段列表 from 表名 order by 排序字段)
where rownum&=5
以scott账户为例，要求：查出emp表中以工资降序的前5名员工的所有信息,查询语句如下：
select * from
(select * from emp order by sal desc)
where rownum&=5
有的朋友可能笑了，这还用得着子查询？我写以下语句不也能搞定吗？
a.select * from emp where rownum&=5 order by sal desc
b.select * from emp order by sal desc
where rownum&=5
以上两种查询语句都是不对的，先说b,大家要明白ORACLE的查询语句也有其固定语法的，如下：
select 字段列表 from 表名 +where 子句
+group by 子句
+having 子句
+order by 子句，所以b不符合语法直接否定，这样的语句运行后会报语句没有正确结束的错误。
而a查询语句执行后，返回的结果并不是我们预期的。这是因为虽然a查询语句虽然符合语法规则，但是逻辑上却不合理。它的逻辑意义是先取出前五条记录然后再排序，显然将我们所要做的TOP N分析的步骤搞反了。
接下来我们继续看看第二种情况，有的朋友可能会从第一种情况中推理出以下语句：
select 字段列表 from
(select 字段列表 from 表名 order by 排序字段)
where rownum&=11 and rownum&=15
毫无疑问，这句查询语句执行后不会有任何结果。要讲明白此查询语句的错误，我们就不得不把rownum拿出来研究研究了。
首先大家要知道rownum是ORACLE在我们查询时自动生成的一个从1开始计数的伪列（就是一个虚假的列，看起来不存在，但是却实实在在存在，呵呵。。。比较难理解吧？要不我怎么说因为这点所以才造成ORACLE TOP N查询的困难呢？大家继续往下看，会慢慢理解的。我也是花了很多时间去理解的。）
这个伪列的产生机理是这样：当我们进行查询操作时，数据库的记录一条一条拿出，并给词条记录自动生成伪列rownum,这里我再强调一下：rownum是从1开始计数的。
那么让我们回头看看上边的查询语句为什么会什么都没有查到。当执行了查询操作后，数据库先拿出一条和查询条件比较。这里的查询条件有两个：rownum&=11 和rownum&=15。查询出的rownum是从1开始计数的，也就是拿出的记录rownum=1.它满足rownum&=15但是不满足rownum&=11。所以被无情的抛弃了。紧接着再拿出一条数据，当然这条数据还会走刚才那条数据的老路：自己的rownum的值被赋予了1，然后与rownum&=11 和rownum&=15的条件比较，结果当然还是被抛弃。这个时候大家也许就会恍然大悟了，如果数据库是这样运行的。那么就永远不会第一次取出rownum&=2的值了，更不用谈&=15了。所以以上的查询语句就不会有查询结果。
要实现TOP N的查询第二种的实现我们不得不发挥我们的聪明才智了。于是有了以下的查询语句：
select * from(select rownum myno,a.* from (select * from emp order by sal desc) a) b where myno&=5 and myno&=10;
以上的语句由内到外划分可分为3部分：
1.select * from emp order by sal desc
这条查询语句是为其查询结果排序的，这个相当容易理解，再次不多讲解。
2.select rownum myno,a.* from (select * from emp order by sal desc) a
这条查询语句将第一条查询语句作为子查询，可以将第一条语句的查询结果作为一个临时表，并且别名为a。这张临时表中的记录与原来的emp表相同，只是全部排序过。
本查询语句的查询结果就是 rownum myno
rownum myno 就是每条记录系统自动给赋予的rownum，读到这里有的朋友会问：为什么要给他起个别名呢？
答案是：不得不起，不然后面的业务没法开展。而且我们之所以能实现TOP N的查询第二种的实现最大的功臣就是这个别名了，继续往下看你就会明白。
a.*就好理解了，指的就是a表的所有内容
3.select * from(select rownum myno,a.* from (select * from emp order by sal desc) a)
b where myno&=5 and myno&=10;
这条语句就是把第二条的查询结果作为子查询，作为一张临时表进行操作，别名为b.
b表中不仅有排序好的emp表的所有记录，此时它还多了一列实体列myno.通过对myno的操作进行查询就没有什么问题了。因为它是本来就存在的，并不是系统在查询时才临时生成的。你可以把myno理解为b表的固有列。如果在第三次查询时你用的是rowno而不是myno进行操作，那么你还会进入系统临时产生rowno的错误当中。
总结一下其核心思想：就是将伪列rowno想办法实例为一个可操作的固有列，通过这个固有列来达到TOP N分析的第二种实现。
随机推荐程序问答结果
如对文章有任何疑问请提交到，或者您对内容不满意，请您反馈给我们发贴求解。
，机器学习分类整理更新日期：： 13:41:39
如需转载，请注明文章出处和来源网址：
本文WWW.DOC100.NET DOC100.NET版权所有。1）首先打开手机【设置】菜单，进入设置菜单后点击【情景模式】...君，已阅读到文档的结尾了呢~~
oracle中文题6-t175p38,oracle面试题,oracle数据b40面试题,oracle练习题,oracle开发面试题,oracle 题库,oracle sql面试题,oracle dba面试题,oracle 多选题,oracle 053题库,oracle 测试题
扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
oracle中文题6-t175p38
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。根据Oracle数据库scott模式下的emp表和dept表_中华文本库
第1页/共4页
题目要求：根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。
（1） 查询20号部门的所有员工信息。
select * from emp where deptno = 20;
（2） 查询所有工种为CLERK的员工的工号、员工名和部门名。
select empno,ename,deptno from emp where job like 'CLERK';
（3） 查询奖金（COMM）高于工资（SAL）的员工信息。
select * from emp where comm &
（4） 查询奖金高于工资的20%的员工信息。
select * from emp where comm & (sal*0.2);
（5） 查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。
select * from emp
where (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');
（6） 查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信
select * from emp
where job not in ('MANAGER','CLERK') and sal &= 2000 ;
（7） 查询有奖金的员工的不同工种。
select distinct job from emp wh
（8） 查询所有员工工资和奖金的和。
select ename,(sal+nvl(comm,0))
（9） 查询没有奖金或奖金低于100的员工信息。
select * from emp where (comm is null or comm & 100) ;
（10） 查询各月倒数第2天入职的员工信息。
select * from emp where hiredate in (select (last_day(hiredate)-1) from emp);
（11） 查询员工工龄大于或等于10年的员工信息。
select * from emp where (sysdate - hiredate)/365 &= 10 ;
（12） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1))
（13） 查询员工名正好为6个字符的员工的信息。
select * from emp where length（ename）= 6 ;
（14） 查询员工名字中不包含字母“S”员工。
select * from emp where ename not in (select ename from emp where ename like '%S%') ; select * from emp where ename not like ‘%S%’;
（15） 查询员工姓名的第2个字母为“M”的员工信息。
select * from emp where ename like '_M%';
（16） 查询所有员工姓名的前3个字符。
select substr(ename,1,3)
（17） 查询所有员工的姓名，如果包含字母“s”，则用“S”替换。
select replace(ename,'s','S')
（18） 查询员工的姓名和入职日期，并按入职日期从先到后进行排列。
select ename,hiredate from emp or
（19） 显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序
select ename,job,sal,comm from emp order by job desc,
第1页/共4页
寻找更多 ""}