SQL之间的行差异 [英] SQL difference between rows

问题描述

我有一个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屋！