Rownum是oracle中的一个伪列,其目的就是给查询的行标注行号。可以实现查询前n行,中间几行,最后几行的功能(根据业 务功能定义行的排序)。但是rownum又是一个很特殊的列,使用过程中,首先要弄清楚oracle的原理,然后加以分析。 注意点: 1.rownum不可以直接在前面加上表名或别名等。 如 select t.rownum from table t;是错误的。 2.rownum和where在同一层查询中,where条件之后使用rownum比较,只能使用<=,,>=,=(使用=,只能是where rownum=1才可以)。否则返回null。如果使用!=或<>,那么只是返回前n-1行,其他按照rownum工作原理推算。 如 select rownum,name from emp where rownum>=5;没有结果 Select rownum,name from emp where rownum=1;返回第1行 Select rownum,name from emp where rownum !=10;//返回1-9行 3.当rownum和排序在一个语句中使用的时候,要注意,看看oracle有没有使用索引,如果使用了索引扫描,那么可能按照索 引来组织数据,如果没有则是先生成行号,然后order by,则查询出来的结果乱序。可以采用嵌套查询,先在内层排序,在 外层查询rownum。 Rownum原理: 1 Oracle executes your query. 1.执行查询操作 2 Oracle fetches the first row and calls it row number 1. 2.将第一行的row num置为1 3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row. 3.将得到的行的row num与条件相比较,如果不匹配,则抛弃行,如果匹配,则返回行 4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth). 4.oracle获取下一行,然后将rownum增1 5 Go to step 3. 5.返回第3步 从这个原理可以知道,select rownum,name from emp where rownum>5;不返回行,因为首先执行查询select name from emp,将第1行的rownum标为1,然后看where条件,为false,则抛弃行,执行第2行,还是rownum标为1,看where条件还为 false,所以永远是false,rownum不改变,所有的行都被抛弃,所以没有结果。 基于以上的问题,那么使用rownum的常用查询结构有: 3. 简单的加上行号 选择员工,显示姓名,结果集的行号 select rownum ,last_name from s_emp; select rownum ,last_name from s_emp where rownum <= 10; select rownum ,last_name from s_emp where rownum = 1; 下面的不正确 select rownum ,last_name from s_emp where rownum = 10; select rownum ,last_name from s_emp where rownum >= 10; 4. 获取前n行 选择员工,显示姓名,行号,按照姓名排序 select rownum,last_name from s_emp order by last_name;--不正确 如果要排序,那么需要在内层查询中排序好,然后外层查询引入rownum(两重嵌套查询) select rownum,last_name from (select last_name from s_emp order by last_name); 选择前10行,可以直接在两重嵌套的外层查询中引入rownum,并且where rownum<= select rownum,last_name from (select last_name from s_emp order by last_name) where rownum<=10; 3.获取中间行数据 选择员工姓名,显示行号,按照姓名排序,显示第5条记录到第10条记录 select rn,last_name from --最外层用where条件判断 (select rownum rn,last_name --中间层查询加上行号 from ( select last_name from s_emp order by last_name --底层查询排序 ) ) where rn between 5 and 10; 也可以在中间层查询中加入最大的行号判断,<=,最外层用>=,上面的查询可以用下面的替换: select rn,last_name from (select rownum rn,last_name from ( select last_name from s_emp order by last_name ) where rownum<=10 --中间层查询把要查询的最大行号最过滤 ) where rn>=5; 4.取最大值的行 查找出薪资差距最大的部门,显示名称 select name from s_dept where id = (select dept_id from (select dept_id, (max(salary) - min(salary)) salary --内层查询将薪水差计算作为别名,排 序 from s_emp group by dept_id order by salary desc) where rownum = 1); --外层查询取得第一行数据 其他的最小,后前行等都可以通