網友的詢問，小喵把自己的回覆整理到Blog，提供有類似網友的參考。上下筆的差異值，先決條件要有上下筆的排序條件，有了之後，透過給順號、SubQuery、自己對自己JOIN的方式，就可以達成，實際的詳細內容，敬請看下去~
緣起
這個問題是在討論區中，一個網友詢問提出的，相關的連結如下：
[SQL]計算與上一筆的差值
在有【指定排序方式】的前提下，這樣的需求是可以做到的，詳細如何處理，請往下繼續看下去~
準備範例資料
我就以自己訂定的資料表來當作範例
資料表的名稱為：TCATDataTime
相關欄位如下：
DTime : 資料的時間 (DateTime)
SData：文字資料(NVarchar(50))
SValue：數字資料(Int)
相關的範例資料表與範例資料的產生語法如下：
CREATE TABLE [dbo].[TCATDataTime](
[DTime] [datetime] NOT NULL,
[SData] [nvarchar](50) NOT NULL,
[SValue] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_TCATDataTime] PRIMARY KEY CLUSTERED
(
[DTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:47:09.497' AS DateTime), N'資料', CAST(123 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:47:25.507' AS DateTime), N'資料', CAST(133 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:22.130' AS DateTime), N'資料', CAST(987 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:33.930' AS DateTime), N'資料', CAST(900 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:49:53.150' AS DateTime), N'資料', CAST(700 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:04.820' AS DateTime), N'資料', CAST(735 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:09.960' AS DateTime), N'資料', CAST(820 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:11.743' AS DateTime), N'資料', CAST(779 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:15.137' AS DateTime), N'資料', CAST(850 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:18.600' AS DateTime), N'資料', CAST(860 AS Decimal(18, 0)))
GO
INSERT [dbo].[TCATDataTime] ([DTime], [SData], [SValue]) VALUES (CAST(N'2020-04-06T09:51:22.053' AS DateTime), N'資料', CAST(920 AS Decimal(18, 0)))
GO
ALTER TABLE [dbo].[TCATDataTime] ADD CONSTRAINT [DF_TCATDataTime_DTime] DEFAULT (getdate()) FOR [DTime]
GO
ALTER TABLE [dbo].[TCATDataTime] ADD CONSTRAINT [DF_TCATDataTime_SData] DEFAULT (N'資料') FOR [SData]
GO
ALTER TABLE [dbo].[TCATDataTime] ADD CONSTRAINT [DF_TCATDataTime_SValue] DEFAULT ((0)) FOR [SValue]
GO
資料就會長類似這樣
2020-04-06 09:47:09.497 資料 123
2020-04-06 09:47:25.507 資料 133
2020-04-06 09:49:22.130 資料 987
2020-04-06 09:49:33.930 資料 900
2020-04-06 09:49:53.150 資料 700
2020-04-06 09:51:04.820 資料 735
2020-04-06 09:51:09.960 資料 820
2020-04-06 09:51:11.743 資料 779
2020-04-06 09:51:15.137 資料 850
2020-04-06 09:51:18.600 資料 860
2020-04-06 09:51:22.053 資料 920
開始處理過程
接著就來說明處理的方式
我們假設以【時間(DTime)排序】來區別【前一筆、後一筆】
首先，依據Row_Number的方式，依據時間排序之後給予編號，並且給予【編號減1】的來做為比對JOIN的Key
請參考以下這篇來設計Row_Number
https://dotblogs.com.tw/topcat/2009/08/06/9906
因此就是這樣的語法來處理
SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
, *
FROM [dbo].[TCATDataTime] (NOLOCK)
這樣可以得到以下這樣的結果
1 0 2020-04-06 09:47:09.497 資料 123
2 1 2020-04-06 09:47:25.507 資料 133
3 2 2020-04-06 09:49:22.130 資料 987
4 3 2020-04-06 09:49:33.930 資料 900
5 4 2020-04-06 09:49:53.150 資料 700
6 5 2020-04-06 09:51:04.820 資料 735
7 6 2020-04-06 09:51:09.960 資料 820
8 7 2020-04-06 09:51:11.743 資料 779
9 8 2020-04-06 09:51:15.137 資料 850
10 9 2020-04-06 09:51:18.600 資料 860
11 10 2020-04-06 09:51:22.053 資料 920
利用SubQuery的方式，讓這樣的資料【自己與自己】JOIN，把其中一個當作是【當下狀態】，另一個當作是【後一個狀態】
於是就可以寫成這樣的語法
SELECT C.ROWID, C.PreROWID, C.SData, C.SValue, ISNULL(P.SValue,0) AS PSValue
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
, *
FROM [dbo].[TCATDataTime] (NOLOCK)
) P
RIGHT OUTER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
, *
FROM [dbo].[TCATDataTime] (NOLOCK)
) C
ON C.PreROWID = P.ROWID
這樣後就可以得到以下這樣的結果
1 0 資料 123 0
2 1 資料 133 123
3 2 資料 987 133
4 3 資料 900 987
5 4 資料 700 900
6 5 資料 735 700
7 6 資料 820 735
8 7 資料 779 820
9 8 資料 850 779
10 9 資料 860 850
11 10 資料 920 860
我們把下一筆的數值，成功地放在上一筆相同Row，不同的Column中
到這邊，我們要的結果已經呼之欲出了
接著，就只要把當下的數值-前一筆的數值，就可以得到結果了
SELECT *, SValue-PSValue AS SDiff
FROM
(
SELECT C.ROWID, C.PreROWID, C.SData, C.SValue, ISNULL(P.SValue,0) AS PSValue
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
, *
FROM [dbo].[TCATDataTime] (NOLOCK)
) P
RIGHT OUTER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY DTime) AS ROWID
, (ROW_NUMBER() OVER(ORDER BY DTime)) - 1 AS PreROWID
, *
FROM [dbo].[TCATDataTime] (NOLOCK)
) C
ON C.PreROWID = P.ROWID
) V
最後得到的結果如下：
1 0 資料 123 0 123
2 1 資料 133 123 10
3 2 資料 987 133 854
4 3 資料 900 987 -87
5 4 資料 700 900 -200
6 5 資料 735 700 35
7 6 資料 820 735 85
8 7 資料 779 820 -41
9 8 資料 850 779 71
10 9 資料 860 850 10
11 10 資料 920 860 60
到此，要的前後筆差異值就此得出。
以上小喵自己筆記，也同時提供網友們參考
^_^
以下是簽名：
- 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
- 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
| Microsoft MVP Visual Studio and Development Technologies (2005~2019/6) | topcat Blog:http://www.dotblogs.com.tw/topcat |
