lead : 返回下一条记录hire_date的值。
SELECT last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99lead : 返回前一条记录hire_date的值(没有的话,值为0)。
SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600sql的帮助需要下在,我有英文版的,需要的话,mail
SELECT last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99lead : 返回前一条记录hire_date的值(没有的话,值为0)。
SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600sql的帮助需要下在,我有英文版的,需要的话,mail
解决方案 »
- 把查询的结果作为数据参数输出,创建过程后提示带警告
- PROC 提取视图数据为空
- oracle 数据库本地怎么开启web 中的http://127.0.0.1:1158/em
- 高分求教PL/SQL Developer有没有版本控制器插件!!
- 在线等,关于字符串问题,急急急急急急急急急急急急急!!!!!!!!!!!!!!!!!!!!!!!!!!
- oracle中遇到的棘手问题!
- 用proc执行存储过程?
- 急!oracle启动突然出现问题~
- win7 64位系统 vb.net连接oracle问题 急求!!!!!!!
- 关于oracle中connectby和rownum的问题
- 关于ORACLE与SQLSERVER左连接的疑问,请高手看看
- 在VB中用DataGrid显示Oracle9i数据表,将表中的代码转换成另一表中代码对应的信息。
it provides access to more than one row of a table at the same time without
a self-join. Given a series of rows returned from a query and a position of
the cursor,LAG/LEAD provides access to a row at a given physical offset prior to
that position if you do not spcify offset, its default is 1. The optional default value is
returned if the offset goes beyond the scope of the window. If you do not
specify default,its default value is null. You cannot use LAG/LEAD or any other analytic function for value_expr. That is,
you can use other built-in function expressions for expr,but you cannot nest
analytic functions.EXAMPLE:LAG:
--The following example provides,for each salesperson in the emp table,the salary
--of the employee hired just before:SELECT ename,hiredate,sal,LAG(sal,1,0) OVER (ORDER BY hiredate) as prev_sal
FROM emp
WHERE job='SALESMAN';
RNAME HIREDATE SAL PREV_SAL
------------------------------------------------------------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 1600
TURNER 08-FEB-81 1500 1250
MARTIN 28-FEB-81 1250 1500
LEAD:--The following example provides,for each employee in the emp table,the hiredate
--of the employee hired just after:
SELECT ename,hiredate,LEAD(hiredate,1) OVER(ORDER BY hiredate) AS "NextHired"
FROM emp;
RNAME HIREDATE NextHired
---------------------------------------------------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81 08-SEP-81
TURNER 08-SEP-81 28-SEP-81
MARTIN 28-SEP-81 17-NOV-81
KING 17-NOV-81 03-DEC-81
JAMES 03-DEC-81 03-DEC-81
FORD 03-DEC-81 23-JAN-82
MILLER 23-JAN-82 19-APR-87
SCOTT 19-APR-87 23-MAY-87
ADAMS 23-MAY-87
http://www.adp-gmbh.ch/ora/sql/analytical/lag.html其实有什么问题上网搜一下就可以了,oracle有很多文档,但还是搜索来得快些:)
我装了oracle 为何帮助里查不到函数的帮助呀??