随笔-314  评论-209  文章-0  trackbacks-0

转自:http://blog.csdn.net/wh62592855/article/details/4988336
例如说吧,对DEPTNO 10中的每个员工,确定聘用他们的日期及聘用下一个员工(可能是其他部门的员工)的日期之间相差的天数。

SQL> select ename,hiredate,deptno from emp order by hiredate;

ENAME      HIREDATE            DEPTNO
---------- --------------- ----------
SMITH      17-DEC-80               20
ALLEN      20-FEB-81               30
WARD       22-FEB-81               30
JONES      02-APR-81               20
BLAKE      01-MAY-81               30
CLARK      09-JUN-81               10
TURNER     08-SEP-81               30
MARTIN     28-SEP-81               30
KING       17-NOV-81               10
JAMES      03-DEC-81               30
FORD       03-DEC-81               20

ENAME      HIREDATE            DEPTNO
---------- --------------- ----------
MILLER     23-JAN-82               10
SCOTT      19-APR-87               20
ADAMS      23-MAY-87               20

14 rows selected.

 

SQL> select ename,hiredate,next_hd,
  2  next_hd-hiredate diff
  3  from
  4  (
  5  select deptno,ename,hiredate,
  6  lead(hiredate) over(order by hiredate) next_hd
  7  from emp
  8  )
  9  where deptno=10;

ENAME      HIREDATE        NEXT_HD               DIFF
---------- --------------- --------------- ----------
CLARK      09-JUN-81       08-SEP-81               91
KING       17-NOV-81       03-DEC-81               16
MILLER     23-JAN-82       19-APR-87             1912

这里的LEAD OVER非常有用,它能够访问“未来的”行(“未来的”行相对于当前行,由ORDER BY子句决定)。这种无需添加联接就能够访问当前行附近行的功能,提高了代码的可读性和有效性。在采用窗口函数时,一定要记住,它在WHERE子句之后求值,因此在该解决方案中,需要使用内联视图。如果把对DEPTNO的筛选移到内联视图,则结果会发生改变(仅考虑了DETPNO 10中的HIREDATE)。

所以下面的结果是错误的:

SQL> select ename,hiredate,next_hd,
  2  next_hd-hiredate diff
  3  from
  4  (
  5  select deptno,ename,hiredate,
  6  lead(hiredate) over(order by hiredate) next_hd
  7  from emp
  8  where deptno=10
  9  );

ENAME      HIREDATE        NEXT_HD               DIFF
---------- --------------- --------------- ----------
CLARK      09-JUN-81       17-NOV-81              161
KING       17-NOV-81       23-JAN-82               67
MILLER     23-JAN-82

 

对于ORACLE的LEAD和LAG函数还需要特别注意,它们的结果中可能会有重复。在上面的例子中表EMP内不包含重复的HIREDATE,所以“看起来”似乎没有什么问题。下面我们向表中插入4个重复值来看看

SQL> insert into emp(empno,ename,deptno,hiredate)
  2  values(1,'a',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)
  2  values(2,'b',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)
  2  values(3,'c',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)
  2  values(4,'d',10,to_date('17-NOV-1981'));

1 row created.

SQL> select ename,hiredate
  2  from emp
  3  where deptno=10
  4  order by 2;

ENAME      HIREDATE
---------- ---------------
CLARK      09-JUN-81
b          17-NOV-81
c          17-NOV-81
a          17-NOV-81
d          17-NOV-81
KING       17-NOV-81
MILLER     23-JAN-82

7 rows selected.

现在还是用以前那个查询语句来试试

SQL> select ename,hiredate,next_hd,
  2  next_hd-hiredate diff
  3  from
  4  (
  5  select deptno,ename,hiredate,
  6  lead(hiredate) over(order by hiredate) next_hd
  7  from emp
  8  )
  9  where deptno=10;

ENAME      HIREDATE        NEXT_HD               DIFF
---------- --------------- --------------- ----------
CLARK      09-JUN-81       08-SEP-81               91
d          17-NOV-81       17-NOV-81                0
c          17-NOV-81       17-NOV-81                0
a          17-NOV-81       17-NOV-81                0
b          17-NOV-81       17-NOV-81                0
KING       17-NOV-81       03-DEC-81               16
MILLER     23-JAN-82       19-APR-87             1912

7 rows selected.

可以看到其中有4个员工的DIFF列值都是0,这是错误的,同一天聘用的所有员工都应该跟下一个聘用其他员工的HIREDATE进行计算。

幸运的是ORACLE针对这类情况提供了一个非常简单的措施:当调用LEAD函数时,可以给LEAD传递一个参数,以便准确的指定“未来的”行(是下一行?10行之后?等等)。

select ename,hiredate,next_hd,
next_hd-hiredate diff
from
(
select deptno,ename,hiredate,
lead(hiredate,cnt-rn+1) over(order by hiredate) next_hd
from
(
select deptno,ename,hiredate,
count(*) over(partition by hiredate) cnt,
row_number() over(partition by hiredate order by empno) rn
from emp
where deptno=10
)
)

 

posted on 2011-12-09 10:40 xzc 阅读(4191) 评论(1)  编辑  收藏 所属分类: Oracle

评论:
# re: ORACLE确定当前记录和下一条记录之间的差值 2012-07-12 11:19 | xzc
SQL> select replace(wm_concat(menu_name), ',', '->')
2 from (select level, a.*
3 from mm_menu a
4 start with menu_id = 1021057
5 connect by menu_id = prior super_id
6 order by level desc);

REPLACE(WM_CONCAT(MENU_NAME),'
--------------------------------------------------------------------------------
系统菜单->系统管理->数据质量->业务发展(分公司)->WIFI场景建设效益监控报表  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: