如何获得列字段的两行之间的差异? [英] How to get difference between two rows for a column field?

问题描述

我有一张这样的桌子:

rowInt  Value
2       23
3       45
17      10
9       0
....

列 rowInt 值是整数，但不在具有相同增量的序列中.我可以使用以下 sql 按 rowInt 列出值:

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:

SELECT * FROM myTable ORDER BY rowInt;

这将按 rowInt 列出值.如何获得两行之间的值差异，结果如下:

This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:

rowInt   Value Diff
2        23    22    --45-23
3        45    -35   --10-45
9        0     -45   --0-45
17       10    10    -- 10-0
....

该表在 SQL 2005 (Miscrosoft) 中

The table is in SQL 2005 (Miscrosoft)

推荐答案

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)

考虑一下，在选择中使用子查询(ala Quassnoi 的答案)可能更有效.我会试用不同的版本，并查看执行计划，看看哪个版本在您拥有的数据集大小上表现最佳...

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...

我仍然看到这赢得了选票，尽管很多人不太可能仍然使用 SQL Server 2005.

I still see this garnering votes, though it's unlikely many people still use SQL Server 2005.

如果您可以访问诸如 LEAD() 之类的窗口函数，请改用它...

If you have access to Windowed Functions such as LEAD(), then use that instead...

SELECT
  RowInt,
  Value,
  LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value
FROM
  sourceTable

这篇关于如何获得列字段的两行之间的差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！