[[316397]]

SQL Server從2012版本開始，引入了LEAD和LAG函數(shù)，這兩個函數(shù)可以把之前要關(guān)聯(lián)查詢的方法，改為可直接獲取當(dāng)前數(shù)據(jù)上下相鄰多少行數(shù)據(jù)，可以很方便的對上下相鄰兩行的數(shù)據(jù)進行加減乘除。今天我們就給大家介紹一下這兩個函數(shù)的用法。

LAG函數(shù)

LAG的作用

LAG 以當(dāng)前行之前的給定物理偏移量來提供對行的訪問。 在 SELECT 語句中使用此分析函數(shù)可將當(dāng)前行中的值與先前行中的值進行比較。

LAG的語法

LAG (scalar_expression [,offset] [,default])      OVER ( [ partition_by_clause ] order_by_clause )

參數(shù)解釋

scalar_expression

要根據(jù)指定偏移量返回的值。 這是一個返回單個（標量）值的任何類型的表達式。scalar_expression 不能為分析函數(shù)。

offset

當(dāng)前行（從中獲得取值）后的行數(shù)。 如果未指定，則默認值為 1。 

OVER

為開窗函數(shù)，LAG函數(shù)必須與開窗函數(shù)一起使用。

LAG示例 

WITH T AS  
(  
SELECT 1 ID,10 NUM  
UNION ALL  
SELECT 1,20  
UNION ALL  
SELECT 1,30  
UNION ALL  
SELECT 2,40  
UNION ALL  
SELECT 2,50  
UNION ALL  
SELECT 2,60  
)  
SELECT ID,NUM,  
LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,  
LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,  
LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs  
FROM T 

(提示：可以左右移動代碼)

結(jié)果如下：

從上面的示例中我們可以看到

1、針對列OneArgs，組內(nèi)的NUM列的值默認向后偏移了一行，每組的第一行用默認的NULL來代替

2、針對TowArgs，使用了2個參數(shù)顯示的偏移行，NUM的值也是向后偏移一行。

3、針對ThreeArgs，不僅使用了顯示的偏移2行，而且第三個參數(shù)將偏移后默認值NULL改成了0

實戰(zhàn)例子：如何求解組內(nèi)上下兩行的和？ 

SELECT ID,NUM,  
NUM+LAG(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM) AS Result  
FROM T 

結(jié)果如下：

注意：第一行因為默認是0，所以每組第一行的結(jié)果是NUM+0=NUM

LEAD函數(shù)

LEAD函數(shù)與LAG函數(shù)剛剛相反，它是向前偏移指定的行數(shù)，默認是1行。

語法哈參數(shù)與LAG類似，這里就不重復(fù)介紹了。我們直接看示例： 

SELECT ID,NUM,  
LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,  
LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,  
LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs  
FROM T 

結(jié)果：

使用情況與LAG函數(shù)類似，只是組內(nèi)數(shù)據(jù)分別向前偏移了指定行數(shù)。

實戰(zhàn)示例：求解同組內(nèi)上下兩行的差？ 

SELECT ID,NUM,  
LEAD(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM)-NUM AS Result  
FROM T 

結(jié)果：

每組最后一行默認是0，所以0-NUM=-NUM

以上就是這兩個函數(shù)的相關(guān)用法。