功能描述:可以访问结果集中的其它行而不用进行自连接。它允许去处理游标,就好像游标是一个数组一样。在给定组中可参考当前行之前的行,这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数,其默认值为1,若索引超出窗口的范围,就返回默认值(默认返回的是组中第一行),其相反的函数是LEAD
SAMPLE:下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
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 ' ;
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-5月 -95 3100 0
Tobias 24-7月 -97 2800 3100
Baida 24-12月-97 2900 2800
Himuro 15-11月-98 2600 2900
Colmenares 10-8月 -99 2500 2600
LEAD
功能描述:LEAD与LAG相反,LEAD可以访问组中当前行之后的行。Offset是一个正整数,其默认值为1,若索引超出窗口的范围,就返回默认值(默认返回的是组中第一行)
SAMPLE:下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值
SELECT
last_name, hire_date,
LEAD(hire_date, 1 ) OVER ( ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30 ;
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-99
上面种我在SQL里试了下..好像要出错..说无法识别LAG和LEAD
还是用这种方法:
/**/
/*比如你知道 ID = 50
上一条 and 下一条 */
select top 1 p_title from t_photo where p_id < 50 order by p_id desc
select top 1 p_title from t_photo where p_id > 50 order by p_id asc
上一条 and 下一条 */
select top 1 p_title from t_photo where p_id < 50 order by p_id desc
select top 1 p_title from t_photo where p_id > 50 order by p_id asc