具有相同ID的多行之间的SQL差异

Question

SQL Server 2012

原始数据

ID   VAL   Time
+---+----+---------------------+
| 2 |  1 | 2015-05-09 12:54:39 |
| 3 | 10 | 2015-05-09 12:54:39 |
| 2 |  1 | 2015-05-09 12:56:39 |
| 3 | 10 | 2015-05-09 12:56:39 |
| 2 |  5 | 2015-05-09 13:48:30 |
| 3 | 16 | 2015-05-09 13:48:30 |
| 2 |  7 | 2015-05-09 15:01:09 |
| 3 | 20 | 2015-05-09 15:01:09 |
+---+----+---------------------+ 

我有一个表，其中 VAL 随时间不断增加。我想操纵数据来显示每个 ID 随着时间的推移 VAL 增加了多少。所以时间 2 的值 - 时间 1 的值

理想结果:

ID   VALI   Time
+---+----+---------------------+
| 2 |  0 | 2015-05-09 12:56:39 |
| 3 |  0 | 2015-05-09 12:56:39 |
| 2 |  4 | 2015-05-09 13:48:30 |
| 3 |  6 | 2015-05-09 13:48:30 |
| 2 |  2 | 2015-05-09 15:01:09 |
| 3 |  4 | 2015-05-09 15:01:09 |
+---+----+---------------------+ 

到目前为止的代码:

select
t1.Time,t1.[ID],t2.[VAL]-t1.[VAL] AS [ValI]
from #tempTable t1
inner join #tempTable t2 ON t1.[ID]=t2.[ID]
AND t1.[Time]<t2.[Time]

我需要计算当前时间戳与当前时间戳之前的时间之间的差异，而不是当前时间戳之前的所有时间戳。到目前为止，当 VAL 没有改变时，我得到了很多重复值。

Answer 1

你可以使用这个。

DECLARE @MyTable TABLE (ID INT,   VAL INT,  [Time] DATETIME)
INSERT INTO @MyTable VALUES
(2,  1 ,'2015-05-09 12:54:39'),
(3, 10 ,'2015-05-09 12:54:39'),
(2,  1 ,'2015-05-09 12:56:39'),
(3, 10 ,'2015-05-09 12:56:39'),
(2,  5 ,'2015-05-09 13:48:30'),
(3, 16 ,'2015-05-09 13:48:30'),
(2,  7 ,'2015-05-09 15:01:09'),
(3, 20 ,'2015-05-09 15:01:09')


;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Time]) RN FROM @MyTable
)
SELECT T1.ID, T2.VAL - T1.VAL AS VALI, T2.Time FROM CTE T1
    INNER JOIN CTE T2 ON T1.ID = T2.ID AND T1.RN = T2.RN - 1
ORDER BY T1.[Time], T1.ID

结果:

ID          VALI        Time
----------- ----------- -----------------------
2           0           2015-05-09 12:56:39.000
3           0           2015-05-09 12:56:39.000
2           4           2015-05-09 13:48:30.000
3           6           2015-05-09 13:48:30.000
2           2           2015-05-09 15:01:09.000
3           4           2015-05-09 15:01:09.000