Oracle数据库入门之多表连接与子查询

核心提示：概述：即获得按照某种规则排序之后的前n条的记录，Oracle中通常采用子查询的方式实现TOPN查询其实子查询可以认为是查到了一个临时表，Oracle数据库入门之多表连接与子查询(3)，或没有名字的临时视图语法：select 字段列表 from (select 字段列表 from table order by 排序字段)

概述：即获得按照某种规则排序之后的前n条的记录。Oracle中通常采用子查询的方式实现TOPN查询
其实子查询可以认为是查到了一个临时表，或没有名字的临时视图
语法：select 字段列表 from (select 字段列表 from table order by 排序字段) where rownum<=n;
举例：select * from (select * from emp order by sal desc) where rownum <=5;



伪列rownum
概述：SELECT查询结果中会隐含的增加一个字段rownum，即伪列。rownum用起来很灵活，但也很容易出错
rownum伪列并不是数据表中或者子查询的虚拟表中真实存在的列，它只是查询结果中的一个伪列
它标记的是符合查询条件的结果的编号，第一条记录的rownum值为1，第二条记录的rownum值为2
可以理解为，符合查询条件的第一行记录编号为1，符合查询条件的第二行记录编号为2
例一：select * from emp where rownum>=5;--它执行后的结果是没有返回值
执行时先取出结果集中的，或者说是数据表中的第一条记录，并标记第一条记录的编号为1
判断后得知1小于5，不符合条件。随后便过滤掉这条记录了，接着判断下一条记录是不是符合条件
于是就又取出下一条记录，下一条记录的rownum还是从1开始。而rownum永远是从1开始的，结果可想而知
接着的下一条记录的编号还是1。即照此情形下去，记录的编号永远不会符合大于等于5的条件
也就是说在这条SQL语句的环境下，rownum永远也不会大于等于5。所以就不能指望用rownum进行区间排序
也就是说rownum>=5 and rownum <=10是永远也不会成立的。这就是所谓的TopN分析
例二：select * from emp where rownum<=5 order by sal desc;
该句执行后并不会得到预期的结果。虽然也会得到5条记录，但并不是工资降序排列后的前5个值
它返回的是emp表中的前5行记录，只不过显示的时候是按照工资进行降序排列之后的效果
执行时会先对where条件进行过滤，过滤后得到了原表中的前5条记录。然后再对表的前5条记录排序并输出
很显然这并不是我们想要得到的。我们希望的是先排序，排好了顺序之后再获取前面的5行信息
但若写成select * from emp order by sal desc where rownum<=5;的话，是不符合select语法的，会出错
所以只能通过子查询的方式在一条语句中结合rownum伪列来实现TopN查询
分页：在JavaWeb编程中，经常会遇到分页显示的问题。有时需要在某一页显示一个区间的记录
比如显示第21条到第30条记录。在这种情况下，单纯的TopN查询显然不能满足要求
这时可以让子查询中的伪列变成一个真实存在的列，或者说让它变成能够进行比较运算的真实的列
述一：select rownum, a.* from (select * from emp order by sal desc) a;
这里如果将a.*写成*的话，就会出现缺失表达式的错误。而子查询不是真实的表，所以只能靠它的别名
它的运行结果是显示原emp中的所有记录，而且还多出了一列ROWNUM的记录，列值是从1到14的连续数字
此时的rownum还是虚的，仍然不能执行where rownum>=5 and rownum<=10的区间排序
因为rownum实际上是等于本次查询14行记录中的每一行记录的伪列号
从第一行开始永远等于1，如果不符合大于等于5的条件的话，第一行记录就会被过滤掉了
而下一行记录的伪列号还是从1开始的，便又会出现“例一”中的结果，所以此时仍不能进行区间排序
述二：这时可以给rownum起一个别名，如myno。然后再把刚才的整条语句作为一个子查询
即select * from (select rownum myno, a.* from (select * from emp order by sal desc) a);
整个括号括起来的又充当了一个子查询。这个子查询会得到n+1条记录，其中第一条记录是myno字段