I need to calculate the difference of a column between two lines of a table. Is there any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008.
我需要计算表的两行之间一列的差值。有什么方法可以直接用SQL实现吗?我使用的是Microsoft SQL Server 2008。
I'm looking for something like this:
我在找这样的东西:
SELECT value - (previous.value) FROM table
Imagining that the "previous" variable reference the latest selected row. Of course with a select like that I will end up with n-1 rows selected in a table with n rows, that's not a probably, actually is exactly what I need.
想象“先前”变量引用最近选中的行。当然，有了这样的选择，我最终会在一个有n行的表中选择n-1行，这不是可能的，实际上这正是我需要的。
Is that possible in some way?
这在某种程度上可能吗?
7 个解决方案
#1
43
SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:
SQL没有内置的顺序概念，因此需要按某个列进行排序，这样才有意义。是这样的:
select t1.value - t2.value from table t1, table t2
where t1.primaryKey = t2.primaryKey - 1
If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.
如果您知道如何排序，但不知道如何获得当前的值(例如，您想按字母顺序排序)，那么我不知道在标准SQL中有什么方法可以做到这一点，但是大多数SQL实现都有扩展来完成它。
Here is a way for SQL server that works if you can order rows such that each one is distinct:
下面是SQL server的一种工作方式，如果您可以对行进行排序，使每个行都是不同的:
select rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t
select t1.value - t2.value from temp1 t1, temp1 t2
where t1.Rank = t2.Rank - 1
drop table temp1
If you need to break ties, you can add as many columns as necessary to the ORDER BY.
如果需要断开连接，可以根据需要向ORDER BY添加尽可能多的列。
#2
28
Use the lag function:
使用延迟函数:
SELECT value - lag(value) OVER (ORDER BY Id) FROM table
Sequences used for Ids can skip values, so Id-1 does not always work.
用于Ids的序列可以跳过值，因此Id-1并不总是有效。
#3
24
Oracle, PostgreSQL, SQL Server and many more RDBMS engines have analytic functions called LAG and LEAD that do this very thing.
Oracle、PostgreSQL、SQL Server和许多RDBMS引擎都有一种叫做LAG和LEAD的分析函数来完成这一任务。
In SQL Server prior to 2012 you'd need to do the following:
在2012年之前的SQL Server中，您需要执行以下操作:
SELECT value - (
SELECT TOP 1 value
FROM mytable m2
WHERE m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
ORDER BY
col1, pk
)
FROM mytable m1
ORDER BY
col1, pk
, where COL1 is the column you are ordering by.
，其中COL1是要排序的列。
Having an index on (COL1, PK) will greatly improve this query.
拥有一个索引(COL1, PK)将极大地改进这个查询。
#4
23
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
value
FROM table
)
SELECT
curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1
#5
8
LEFT JOIN the table to itself, with the join condition worked out so the row matched in the joined version of the table is one row previous, for your particular definition of "previous".
左侧连接到表本身，与JOIN条件一起工作，因此表的连接版本中匹配的行是前面的一行，因为您对“以前”的特定定义。
Update: At first I was thinking you would want to keep all rows, with NULLs for the condition where there was no previous row. Reading it again you just want that rows culled, so you should an inner join rather than a left join.
更新:一开始，我认为您需要保留所有的行，对于没有前一行的情况，使用NULLs。再读一遍，您只需要删除行，因此应该使用内部连接，而不是左连接。
Update:
更新:
Newer versions of Sql Server also have the LAG and LEAD Windowing functions that can be used for this, too.
更新版本的Sql Server也有延迟和引导窗口功能，也可以用于此。
#6
2
The selected answer will only work if there are no gaps in the sequence. However if you are using an autogenerated id, there are likely to be gaps in the sequence due to inserts that were rolled back.
选择的答案只有在序列中没有空隙时才有效。但是，如果您正在使用一个自动生成的id，由于被回滚的插入，序列中可能会有间隙。
This method should work if you have gaps
如果有间隙，这个方法应该有效
declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by primarykey
select t1.value - t2.value from @temp t1
join @temp t2
on t1.tempid = t2.tempid - 1
#7
2
select t2.col from (
select col,MAX(ID) id from
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2
#1
43
SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:
SQL没有内置的顺序概念，因此需要按某个列进行排序，这样才有意义。是这样的:
select t1.value - t2.value from table t1, table t2
where t1.primaryKey = t2.primaryKey - 1
If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.
如果您知道如何排序，但不知道如何获得当前的值(例如，您想按字母顺序排序)，那么我不知道在标准SQL中有什么方法可以做到这一点，但是大多数SQL实现都有扩展来完成它。
Here is a way for SQL server that works if you can order rows such that each one is distinct:
下面是SQL server的一种工作方式，如果您可以对行进行排序，使每个行都是不同的:
select rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t
select t1.value - t2.value from temp1 t1, temp1 t2
where t1.Rank = t2.Rank - 1
drop table temp1
If you need to break ties, you can add as many columns as necessary to the ORDER BY.
如果需要断开连接，可以根据需要向ORDER BY添加尽可能多的列。
#2
28
Use the lag function:
使用延迟函数:
SELECT value - lag(value) OVER (ORDER BY Id) FROM table
Sequences used for Ids can skip values, so Id-1 does not always work.
用于Ids的序列可以跳过值，因此Id-1并不总是有效。
#3
24
Oracle, PostgreSQL, SQL Server and many more RDBMS engines have analytic functions called LAG and LEAD that do this very thing.
Oracle、PostgreSQL、SQL Server和许多RDBMS引擎都有一种叫做LAG和LEAD的分析函数来完成这一任务。
In SQL Server prior to 2012 you'd need to do the following:
在2012年之前的SQL Server中，您需要执行以下操作:
SELECT value - (
SELECT TOP 1 value
FROM mytable m2
WHERE m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
ORDER BY
col1, pk
)
FROM mytable m1
ORDER BY
col1, pk
, where COL1 is the column you are ordering by.
，其中COL1是要排序的列。
Having an index on (COL1, PK) will greatly improve this query.
拥有一个索引(COL1, PK)将极大地改进这个查询。
#4
23
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
value
FROM table
)
SELECT
curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1
#5
8
LEFT JOIN the table to itself, with the join condition worked out so the row matched in the joined version of the table is one row previous, for your particular definition of "previous".
左侧连接到表本身，与JOIN条件一起工作，因此表的连接版本中匹配的行是前面的一行，因为您对“以前”的特定定义。
Update: At first I was thinking you would want to keep all rows, with NULLs for the condition where there was no previous row. Reading it again you just want that rows culled, so you should an inner join rather than a left join.
更新:一开始，我认为您需要保留所有的行，对于没有前一行的情况，使用NULLs。再读一遍，您只需要删除行，因此应该使用内部连接，而不是左连接。
Update:
更新:
Newer versions of Sql Server also have the LAG and LEAD Windowing functions that can be used for this, too.
更新版本的Sql Server也有延迟和引导窗口功能，也可以用于此。
#6
2
The selected answer will only work if there are no gaps in the sequence. However if you are using an autogenerated id, there are likely to be gaps in the sequence due to inserts that were rolled back.
选择的答案只有在序列中没有空隙时才有效。但是，如果您正在使用一个自动生成的id，由于被回滚的插入，序列中可能会有间隙。
This method should work if you have gaps
如果有间隙，这个方法应该有效
declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by primarykey
select t1.value - t2.value from @temp t1
join @temp t2
on t1.tempid = t2.tempid - 1
#7
2
select t2.col from (
select col,MAX(ID) id from
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2