SQL之间的行差异 [英] SQL difference between rows
本文介绍了SQL之间的行差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个SQL 2008数据库表,像这样
I have a SQL 2008 database table like this
name score
-----------------------
steve 207
steve 205
steve 200
steve 139
我想得到行之间的区别。 eqn = [row - (row + 1)]
所以我希望它是,
I want to get the difference between the rows. eqn = [row - (row + 1)] so I would ideally want it to be,
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!
推荐答案
这是一种方法
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
这篇关于SQL之间的行差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文