[SQL]計算與上一筆的差值

### 準備範例資料

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

### 開始處理過程

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

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

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