實戰:SQL sever如何實現同一列上下行運算?

今天講講資料庫SQL server的相關知識，如果要將一個表裡的兩列進行運算，那麼我們可以直接用欄位進行運算，如A+B或A-B，其中A和B是兩個不同列的欄位名。

SQL Server 知識

但是在項目中，有時候我們會遇到一些棘手的問題，做數據處理的時候，可能需要實現同一列的上下行運算。那麼在此樓主提供兩種思路：

一、SQL sever2012以上版本，可以用LAG函數。LAG的作用是，從當前行向下偏移指定的行數。 在 SELECT 語句中使用此分析函數可將當前行中的值與先前行中的值進行比較。使用語法如下：

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

其中，scalar_expression是指要根據指定偏移量返回的值。 這是一個返回單個（標量）值的任何類型的表達式，不能為分析函數。offset指當前行（從中獲得取值）後的行數。 如果未指定，則默認值為 1。 OVER為開窗函數，LAG函數必須與開窗函數一起使用。為了說明更加具體一點，我們做個測試表，取名為T：

建立測試表

然後執行SQL語句：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 ThressArgsFROM T，再看結果：

偏移後的數據顯示

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

針對列OneArgs，組內的NUM列的值默認向下偏移了一行，每組的第一行用NULL來代替

針對TowArgs，offset參數傳1，NUM的值也是向下偏移一行，結果和OneArgs一樣。

針對ThreeArgs，offset參數傳2，而且第三個參數將偏移後默認值NULL改成了0。

明白了LAG函數的原理，我們看看怎麼實現上下行相加的運算：

SQL語句：SELECT ID,NUM,

NUM+LAG(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM) AS ResultFROM T

同列上下行相加運算

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

與LAG函數相對應的，還有個函數叫LEAD，此函數與LAG函數剛剛相反，它是向上偏移指定的行數，默認是1行。使用語法同LAG函數類似，此處不再贅述，直接看結果：

LEAD函數執行結果向上偏移一行

那麼我們很容易解答一個問題：如何求解同組內上下兩行的差？語句如下：

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

同列上下行相減運算

運算規則可以自己寫，主要是要弄明白你想讓數據向上偏移還是向下偏移。

二、SQL sever2012以下版本，嘗試用臨時表拆分數據之後再比對的方式。

以上面的表為例，首選需要做個臨時表，這個錶帶有唯一標示號，我們取欄位名為xh：

做出新的臨時表

再執行SQL語句：SELECT a.ID,a.NUM,b.NUM-a.NUM FROM TA a

left join (SELECT xh-1 as xh,ID,NUM from TA) b on a.xh =b.xh AND a.ID=b.ID

用臨時表的方法算出結果

總結：這兩種雖然寫法和語法都不一樣，但是思路是一樣的，就是先把一個列的數據向上或向下偏移，偏移後形成一個新的列，然後再進行兩列的運算，得到我們想要的結果。

好了今天的SQL知識暫時就分享到這裡，有疑問的讀者可以評論區留言，歡迎關注我的百家號"IT圈不亂"。