行之间的 SQL 差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6299950/
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
SQL difference between rows
提问by cwhelms
I have a SQL 2008 database table like this
我有一个像这样的 SQL 2008 数据库表
name score
-----------------------
steve 207
steve 205
steve 200
steve 139
I want to get the difference between the rows. eqn = [row - (row + 1)] so I would ideally want it to be,
我想得到行之间的差异。eqn = [row - (row + 1)] 所以我希望它是，
steve 2 //207 - 205
steve 5 //205 - 200
steve 61 //200 - 139
steve 139 //139 - 0
What is the best way to do this? Thanks!
做这个的最好方式是什么？谢谢！
回答by Conrad Frix
This is one way to do it
这是一种方法
with cte as
(SELECT
ROW_NUMBER() OVER (PARTITION BY table.name ORDER BY id) row,
name,
score
FROM table)
SELECT
a.name ,
a.score - ISNULL(b.score,0)
FROM
cte a
LEFT JOIN cte b
on a.name = b.name
and a.row = b.row+1
回答by Denis de Bernardy
Another way to do it is using lag():
另一种方法是使用lag()：
SELECT
name,
score - coalesce(lag(score) over (partition by name order by id), 0)
FROM table
ORDER BY name, id
The lag() function doesn't seem to be availablein TSQL but this workaround is suggested in the comments:
TSQL 中似乎不提供该 lag() 函数，但在评论中建议使用此解决方法：
select name,
score - coalesce(
MIN(score) OVER (PARTITION BY name ORDER BY id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
, 0) as diff
from table
order by name, id
