into l_element_link_id,l_object_version_number,l_effective_start_date
from pay_element_links_f pelf
where pelf.payroll_id=l_payroll_id
and pelf.element_type_id=l_element_type_id
and nvl(pelf.organization_id,0)=nvl(l_organization_id,0)
-- and nvl(pelf.location_id,0)=nvl(l_location_id,0)
-- and nvl(pelf.grade_id,0)=nvl(l_grade_id,0)
-- and nvl(pelf.position_id,0)=nvl(l_position_id,0)
and nvl(pelf.people_group_id,0)=nvl(l_people_group_id,0)
and rownum=1 --只取第一条记录
and pelf.effective_end_date=to_date(substr('4712-12-31',1,10),'YYYY-MM-DD')
ORDER BY nvl(pelf.object_version_number,-1) desc;
--此语句是有问题的,rownum 是不支持order by 排序的。因为rownum是在排序之前取值的。
1.oracle手册上说的:“即使select语句中一条简单的order by都可能会搞乱ROWNUM(因为ROWNUM是排序前分配给各行的) ”。
2.在oracle中如果rownum与order by同在,是先rownum,然后再order by
如 select * from 表名 where rownum <10 order by 字段
这样会先取rownum小于10的记录,然后再对这10条进行排序
但是,有文章说如果对排序字段建立了索引,则上面的语句就能产生先排序,再rownum的效果。
但是经过我测试,好像建立索引不行,数据条数的物理存储顺序并没有改变,只有建立主键后,才能实现上述的结果。
有没有其他解决方法能先排序再rownum ,嵌套的方法不算,如下这样的
select * from (select * from 表明 order by 字段) where rownum < 10
3.讨论rownum和order by是没有意义的
rownum是一个伪列,和数据的组织方式相关,它不能唯一标识记录
如果你没有定义主键或能唯一标识记录的字段的话,唯一标识记录的是rowid,插入一条记录后就为有个唯一的rowid来标识,且rowid不会再改变。
但是如果你的表改变了表空间的话,rowid也可能会改变
4.1. "在oracle中如果rownum与order by同在,是先rownum,然后再order by " ,这个论述是对的.
2.可以借助于oracle 的row_number 分析函数来实现zl 的需求.
SQL > select * from test a;
ID NAME
---------- ----------
1 lasdjfla
3 lasdje23
3 kk
3 bbb
2 lasdje23
2 aaa
6 rows selected
SQL > select a.* from test a where rownum <6;
ID NAME
---------- ----------
1 lasdjfla
3 lasdje23
3 kk
3 bbb
2 lasdje23
SQL > select a.* ,row_number() over ( order by id ) rn from test a where rownum <6;
ID NAME RN
---------- ---------- ----------
1 lasdjfla 1
2 lasdje23 2
3 lasdje23 3
3 kk 4
3 bbb 5
SQL >
想要解决和order by的结合使用问题,可以用内嵌视图:inline view
如:
select *
from(
select empno,job,mgr
from emp
order by mgr desc)a
--此a代表的视为内嵌视图:inline view:并不是存储在DB中的命名视图
where rownum<=5;
例子很简单 scott用户下,对emp表的操作
主要通过这个例子来更好的理解关于order by 结合使用rownum的情况
例子如下:
select *
from(
select empno,job,mgr
from emp
order by mgr desc)a
--此a代表的视为内嵌视图:inline view:并不是存储在DB中的命名视图
where rownum<=5;
EMPNO JOB MGR
---------- --------- ----------
7839 PRESIDENT
7370 CLERK 7902
7597 MANAGER 7839
7699 MANAGER 7839
7813 MANAGER 7839
select *
from(
select empno,job,mgr
from emp
order by mgr)
where rownum<=5;
EMPNO JOB MGR
---------- --------- ----------
7788 ANALYST 7566
7902 ANALYST 7566
7499 SALESMAN 7698
7521 SALESMAN 7698
7844 SALESMAN 7698
======================================================================
以下结果是先返回表的前5条记录,然后按照order by mgr;进行默认的升序排列
======================================================================
select empno,job,mgr
from emp
where rownum<=5
order by mgr;
EMPNO JOB MGR
---------- --------- ----------
7499 SALESMAN 7698
7521 SALESMAN 7698
7654 SALESMAN 7698
7597 MANAGER 7839
7370 CLERK 7902
========================
原始数据:
select empno,job,mgr
from emp;
EMPNO JOB MGR
----- --------- ----------
7370 CLERK 7902
7499 SALESMAN 7698
7521 SALESMAN 7698
7597 MANAGER 7839
7654 SALESMAN 7698
7699 MANAGER 7839
7813 MANAGER 7839
7788 ANALYST 7566
7839 PRESIDENT
7844 SALESMAN 7698
7877 CLERK 7788
7900 CLERK 7698
7902 ANALYST 7566
7965 CLERK 7782