摘要:LEAD (Transact-SQL)
隨手紀錄
比較每一列跟前一列或後一列的差異時
Lead
Lag
Lag剛好跟Lead反過來
適用於：SQL Server (SQL Server 2012 至目前版本)
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
SELECT TOP 10
[LOTNP]
,[AOI_TOTAL]
,LEAD([AOI_TOTAL] , 1,0) OVER (ORDER BY [LOTNP]) AS NextAOI_TOTAL
,([AOI_TOTAL] - (LEAD([AOI_TOTAL] , 1,0) OVER (ORDER BY [LOTNP])))as diff
FROM [CIM].[dbo].[AOI_DATA] WHERE LOTNP <>''
Lead View0
| LOTNP | AOI_TOTAL | NextAOI_TOTAL | diff |
| CE3AK0010 | 809 | 4720 | 89 |
| CE3AK0020 | 4720 | 4782 | -62 |
| CE3AK0020 | 4782 | 4798 | -16 |
| CE3AK0030 | 4798 | 4863 | -65 |
| CE3AK0030 | 4863 | 4856 | 7 |
| CE3AK0030 | 4856 | 4854 | 2 |
| CE3AK0030 | 4854 | 4943 | -89 |
| CE3AK0030 | 4943 | 4944 | -1 |
| CE3AK0030 | 4944 | 4961 | -17 |
| CE3AK0030 | 4961 | 4883 | 78 |
SELECT TOP 10
[LOTNP]
,[AOI_TOTAL]
,lag([AOI_TOTAL] , 1,0) OVER (ORDER BY [LOTNP]) AS NextAOI_TOTAL
,([AOI_TOTAL] - (lag([AOI_TOTAL] , 1,0) OVER (ORDER BY [LOTNP])))as diff
FROM [CIM].[dbo].[AOI_DATA] WHERE LOTNP <>''
Lag View0
| LOTNP | AOI_TOTAL | NextAOI_TOTAL | diff |
| CE3AK0010 | 4809 | 0 | 4809 |
| CE3AK0020 | 4720 | 4809 | -89 |
| CE3AK0020 | 4782 | 4720 | 62 |
| CE3AK0030 | 4798 | 4782 | 16 |
| CE3AK0030 | 4863 | 4798 | 65 |
| CE3AK0030 | 4856 | 4863 | -7 |
| CE3AK0030 | 4854 | 4856 | -2 |
| CE3AK0030 | 4943 | 4854 | 89 |
| CE3AK0030 | 4944 | 4943 | 1 |
| CE3AK0030 | 4961 | 4944 | 17 |
參考
https://msdn.microsoft.com/zh-tw/library/hh231256(v=sql.120).aspx
https://msdn.microsoft.com/zh-tw/library/hh213125(v=sql.120).aspx
大家一起加入blogads 賺零用錢!!