SQL 如何获得列字段的两行之间的差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/634568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get difference between two rows for a column field?
提问by David.Chu.ca
I have a table like this:
我有一张这样的表：
rowInt Value
2 23
3 45
17 10
9 0
....
The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt:
列 rowInt 值是整数，但不在具有相同增量的序列中。我可以使用以下 sql 按 rowInt 列出值：
SELECT * FROM myTable ORDER BY rowInt;
This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:
这将按 rowInt 列出值。如何获得两行之间的值差异，结果如下：
rowInt Value Diff
2 23 22 --45-23
3 45 -35 --10-45
9 0 -45 --0-45
17 10 10 -- 10-0
....
The table is in SQL 2005 (Miscrosoft)
该表在 SQL 2005 (Miscrosoft) 中
采纳答案by MatBailie
SELECT
[current].rowInt,
[current].Value,
ISNULL([next].Value, 0) - [current].Value
FROM
sourceTable AS [current]
LEFT JOIN
sourceTable AS [next]
ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)
EDIT: Thinking about it, using a subquery in the select (ala Quassnoi's answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have...
编辑：考虑一下，在选择（ala Quassnoi 的答案）中使用子查询可能更有效。我会试用不同的版本，并查看执行计划，看看哪个版本在你拥有的数据集大小上表现最好......
回答by Quassnoi
SELECT rowInt, Value,
COALESCE(
(
SELECT TOP 1 Value
FROM myTable mi
WHERE mi.rowInt > m.rowInt
ORDER BY
rowInt
), 0) - Value AS diff
FROM myTable m
ORDER BY
rowInt
回答by Ansonmus
SQL Server 2012 and up support LAG/ LEADfunctions to access the previous or subsequent row. SQL Server 2005 does not support this (in SQL2005 you need a join or something else).
SQL Server 2012 及更高版本支持LAG/ LEAD函数来访问前一行或后一行。SQL Server 2005 不支持此功能（在 SQL2005 中您需要连接或其他东西）。
A SQL 2012 example on this data
此数据的 SQL 2012 示例
/* Prepare */
select * into #tmp
from
(
select 2 as rowint, 23 as Value
union select 3, 45
union select 17, 10
union select 9, 0
) x
/* The SQL 2012 query */
select rowInt, Value, LEAD(value) over (order by rowInt) - Value
from #tmp
LEAD(value) will return the value of the next row in respect to the given order in "over" clause.
LEAD(value) 将根据“over”子句中的给定顺序返回下一行的值。
回答by dance2die
If you really want to be sure of orders, use "Row_Number()" and compare next record of current record (take a close look at "on" clause)
如果您真的想确定订单，请使用“Row_Number()”并比较当前记录的下一条记录（仔细查看“on”子句）
T1.ID + 1 = T2.ID
You are basically joining next row with current row, without specifying "min" or doing "top". If you have a small number of records, other solutions by "Dems" or "Quassanoi" will work fine.
您基本上是将下一行与当前行连接起来，而不指定“min”或“top”。如果您的记录数量很少，“Dems”或“Quassanoi”的其他解决方案也可以正常工作。
with T2 as (
select ID = ROW_NUMBER() over (order by rowInt),
rowInt, Value
from myTable
)
select T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Value
from ( SELECT ID = ROW_NUMBER() over (order by rowInt), *
FROM myTable ) T1
left join T2 on T1.ID + 1 = T2.ID
ORDER BY T1.ID
回答by David Aldridge
Does SQL Server support analytic functions?
SQL Server 是否支持分析功能？
select rowint,
value,
value - lag(value) over (order by rowint) diff
from myTable
order by rowint
/
回答by mns
select t1.rowInt,t1.Value,t2.Value-t1.Value as diff
from (select * from myTable) as t1,
(select * from myTable where rowInt!=1
union all select top 1 rowInt=COUNT(*)+1,Value=0 from myTable) as t2
where t1.rowInt=t2.rowInt-1
回答by arun sivaraman menon
Query to Find the date difference between 2 rows of a single column
查询以查找单列的 2 行之间的日期差异
SELECT
Column name,
DATEDIFF(
(SELECT MAX(date) FROM table name WHERE Column name < b. Column name),
Column name) AS days_since_last
FROM table name AS b
回答by Nosegear
I'd just make a little function for that. Toss in the two values you need to know the difference between and have it subtract the smaller from the larger value. Something like:
我只是为此做一个小功能。将您需要了解的两个值放入其中，并让它从较大的值中减去较小的值。就像是：
CREATE FUNCTION [dbo].[NumDifference]
( @p1 FLOAT,
@p2 FLOAT )
RETURNS FLOAT
AS
BEGIN
DECLARE @Diff FLOAT
IF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1
RETURN @Diff
END
In a query to get the difference between column a and b:
在查询 a 列和 b 列之间的差异时：
SELECT a, b, dbo.NumDifference(a, b) FROM YourTable
