LAG()和LEAD()統(tǒng)計函數(shù)能夠在一次查詢中取出同一字段的前N行的數(shù)據(jù)和后N行的值 。這樣的操作能夠使用對同樣表的表連接來實現(xiàn)，只是使用LAG和 LEAD有更高的效率。下面整理的LAG()和LEAD()樣例：

LAG(EXPRESSION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lag (profit,1) over (order by year)? as 51xit_exp from test;
YEAR REGION????? PROFIT 51xit_exp
---- ------- ---------- -------------
2003 West??????????? 88
2003 West??????????? 88??????????? 88
2003 Central??????? 101??????????? 88
2003 Central??????? 100?????????? 101
2003 East?????????? 102?????????? 100
2004 West??????????? 77?????????? 102
2004 East?????????? 103??????????? 77
2004 West??????????? 89?????????? 103

LEAD(EXPRESION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lead (profit,1) over (order by year)? as next_year_exp from test;
YEAR REGION????? PROFIT NEXT_YEAR_EXP
---- ------- ---------- -------------
2003 West??????????? 88??????????? 88
2003 West??????????? 88?????????? 101
2003 Central??????? 101?????????? 100
2003 Central??????? 100?????????? 102
2003 East?????????? 102??????????? 77
2004 West??????????? 77?????????? 103
2004 East?????????? 103??????????? 89
2004 West??????????? 89

Lag函數(shù)為Lag(exp,N,defval)，defval是當該函數(shù)無值可用的情況下返回的值。Lead函數(shù)的使用方法類似。
Lead和Lag函數(shù)也能夠使用分組，下面是使用region分組的樣例：
SQL> select year,region,profit , lag (profit,1,0) over (PARTITION BY region order by year)??? as 51xit_exp from test;
YEAR REGION????? PROFIT 51xit_exp
---- ------- ---------- -------------
2003 Central??????? 101???????????? 0
2003 Central??????? 100?????????? 101
2003 East?????????? 102???????????? 0
2004 East?????????? 103?????????? 102
2003 West??????????? 88???????????? 0
2003 West??????????? 88??????????? 88
2004 West??????????? 77??????????? 88
2004 West??????????? 89??????????? 77

一SQL問題解答：
問題：
CREATE?? TABLE? ldy_temp_2
(
? 分局??? VARCHAR(255),
派出所??? VARCHAR(255) ,
證件類型??? VARCHAR(255) ,
證件號碼??? VARCHAR(255) ,
姓名??? VARCHAR(255) ,
性別??? VARCHAR(255) ,
行政區(qū)劃??? VARCHAR(255) ,
旅館名稱??? VARCHAR(255) ,
旅館地址??? VARCHAR(255) ,
房間號??? VARCHAR(255) ,
入住時間??? VARCHAR(255) ,
col012??? VARCHAR(255)
);

INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1234','ZHANGTAO','A','20100506');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1234','ZHANGTAO','A','20100507');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1234','ZHANGTAO','B','20100508')；

INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1234','ZHANGTAO','A','20100509');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1235','ZZZZ','A','20100506');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1235','ZZZZ','B','20100507');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1235','ZZZZ','A','20100508');
INSERT INTO LDY_TEMP_2
(證件號碼,姓名,旅館名稱,入住時間)
VALUES('1235','ZZZZ','B','20100509');

建表語句和測試數(shù)據(jù)已經(jīng)給出? 請問? 怎樣查找相鄰兩次入住旅館名稱不同的人；也就是說 一個人的證件號碼是123的話 那么這個人的信息依照入住時間排序后? 相鄰兩條數(shù)據(jù)的旅館名稱不能一樣 。

解答：
with temp_a as
(select
??????? t.證件號碼,
??????? t.旅館名稱,
??????? t.入住時間,
??????? lag(t.旅館名稱) over (partition by t.證件號碼 order by t.入住時間) as lagname
from ldy_temp_2 t)
select 證件號碼,姓名,旅館名稱,入住時間
from ldy_temp_2 a
where a.證件號碼 not in (select b.證件號碼 from temp_a b where? b.旅館名稱=b.lagname)