TSQL - 获取每个行值之间的差异 [英] TSQL - Get the difference between each row value
本文介绍了TSQL - 获取每个行值之间的差异的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我将数据存储在下表中：
[dbo]。[阅读]
$ b $ [id] [int] IDENTITY（1,1）NOT NULL，
[device_id] [int] NOT NULL，
[time] [datetime] NOT NULL，
[reading] [decimal]（18，2）NOT NULL，
[shift_id] [int] NULL，
该值存储在阅读栏中。我想要的是每行之间的读数差异。即如果row1的阅读= 1520和row2的阅读= 1560那么差异应该是40，依此类推的第2行和第3行.....
样本数据如下....
67118 5 2013-02-23 04：21：45.107 1.00 42
67119 5 2013-02-23 04：21：45.247 4.00 42
67120 5 2013-02-23 04：21：45.340 7.00 42
67121 5 2013-02-23 04：21：45.433 9.00 42
67122 5 2013-02-23 04:21 ：45.527 11.00 42
67123 5 2013-02-23 04:21:45.620 13.00 42
67124 5 2013-02-23 04:21:45.713 16.00 42
67125 5 2013-02- 23 04：21：45.807 18.00 42
67126 5 2013-02-23 04：21：45.900 20.00 42
67127 5 2013-02-23 04：21：45.993 22.00 42
67128 5 2013-02-23 04：21：46.087 24.00 42
67129 5 2013-02-23 04：21：46.183 26.00 42
67130 5 2013-02-23 04：21：46.277 28.00 42
67131 5 2013-02-23 04：21：46.370 30.00 42
67132 5 2013-02-23 04：21：46.463 31.00 42
67133 5 2013-02-23 04:21:46 .557 33.00 42
67134 5 2013-02-23 04:21:46.650 35.00 42
67135 5 2013-02-23 04:21:46.743 37.00 42
67136 5 2013-02- 23 04：21：46.837 39.00 42
67137 5 2013-02-23 04：21：46.930 41.00 42
67138 5 2013-02-23 04：21：47.023 43.00 42
67139 5 2013-02-23 04：21：47.117 45.00 42
67140 5 2013-02-23 04：21：47.210 47.00 42
67141 5 2013-02-23 04：21：47.303 49.00 42
67142 5 2013-02-23 04：21：47.400 50.00 42
67143 5 2013-02-23 04：21：47.493 52.00 42
67144 5 2013-02-23 04：21：47.600 55.00 42
67145 5 2013-02-23 04:21:47.693 56.00 42
67146 5 2013-02-23 04:21:47.790 58.00 42
67147 5 2013-02-23 04 ：21：47.883 60.00 42
更多我需要每小时每班每小时的数据，如下所示。我写了存储过程并生成了示例数据。
2013-02-23 1st 4 5 391.00 1.00 390.00
2013-02-23 1st 7 5 2762.00 1019.00 1743.00
2013-02-23 2nd 8 5 3270.00 2764.00 506.00
2013-02-23 3rd 16 5 6255.00 4102.00 2153.00
解决方案 ; WITH cte AS
（
SELECT *，ROW_NUMBER（）OVER（PARTITION BY device_id ORDER BY [time]）as NId
FROM [dbo]。[readings]
）
SELECT c1。[id]，c1。[device_id]，c1。[time]，
c1.reading AS currRow，ISNULL c2.reading，0）as nextRow，
c1.reading - ISNULL（c2.reading，c1.reading）AS diff
FROM cte c1 LEFT JOIN cte c2 ON c1.device_id = c2.device_id
AND c1.NId + 1 = c2.NId
I am storing data in the following table -
[dbo].[readings]
[id] [int] IDENTITY(1,1) NOT NULL,
[device_id] [int] NOT NULL,
[time] [datetime] NOT NULL,
[reading] [decimal](18, 2) NOT NULL,
[shift_id] [int] NULL,
The value is being stored in reading column. What i want is the difference of reading between each row. i.e. if row1 has reading = 1520 and row2 has reading = 1560 then difference should be 40 and so on for row2 and row3 .....
The sample data is as below ....
67118 5 2013-02-23 04:21:45.107 1.00 42
67119 5 2013-02-23 04:21:45.247 4.00 42
67120 5 2013-02-23 04:21:45.340 7.00 42
67121 5 2013-02-23 04:21:45.433 9.00 42
67122 5 2013-02-23 04:21:45.527 11.00 42
67123 5 2013-02-23 04:21:45.620 13.00 42
67124 5 2013-02-23 04:21:45.713 16.00 42
67125 5 2013-02-23 04:21:45.807 18.00 42
67126 5 2013-02-23 04:21:45.900 20.00 42
67127 5 2013-02-23 04:21:45.993 22.00 42
67128 5 2013-02-23 04:21:46.087 24.00 42
67129 5 2013-02-23 04:21:46.183 26.00 42
67130 5 2013-02-23 04:21:46.277 28.00 42
67131 5 2013-02-23 04:21:46.370 30.00 42
67132 5 2013-02-23 04:21:46.463 31.00 42
67133 5 2013-02-23 04:21:46.557 33.00 42
67134 5 2013-02-23 04:21:46.650 35.00 42
67135 5 2013-02-23 04:21:46.743 37.00 42
67136 5 2013-02-23 04:21:46.837 39.00 42
67137 5 2013-02-23 04:21:46.930 41.00 42
67138 5 2013-02-23 04:21:47.023 43.00 42
67139 5 2013-02-23 04:21:47.117 45.00 42
67140 5 2013-02-23 04:21:47.210 47.00 42
67141 5 2013-02-23 04:21:47.303 49.00 42
67142 5 2013-02-23 04:21:47.400 50.00 42
67143 5 2013-02-23 04:21:47.493 52.00 42
67144 5 2013-02-23 04:21:47.600 55.00 42
67145 5 2013-02-23 04:21:47.693 56.00 42
67146 5 2013-02-23 04:21:47.790 58.00 42
67147 5 2013-02-23 04:21:47.883 60.00 42
Further more i need data per day per shift per hours like below. I have written stored procedure and generated the sample data..
2013-02-23 1st 4 5 391.00 1.00 390.00
2013-02-23 1st 7 5 2762.00 1019.00 1743.00
2013-02-23 2nd 8 5 3270.00 2764.00 506.00
2013-02-23 3rd 16 5 6255.00 4102.00 2153.00
解决方案 Concerning first question in SQLServer 2005+ you can try this
;WITH cte AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY device_id ORDER BY [time]) AS NId
FROM [dbo].[readings]
)
SELECT c1.[id], c1.[device_id], c1.[time],
c1.reading AS currRow, ISNULL(c2.reading, 0) AS nextRow,
c1.reading - ISNULL(c2.reading, c1.reading) AS diff
FROM cte c1 LEFT JOIN cte c2 ON c1.device_id = c2.device_id
AND c1.NId + 1 = c2.NId
这篇关于TSQL - 获取每个行值之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
