如何从上一行获取值 [英] How do I get a value from the previous row

问题描述

我想要上一行的值.

带值的myTable

行号姓名工资
1艾伦20000
2艾伦22000
3艾伦24000

我想从最后一行获取以前的值，例如22000

我的表中没有行号，因此请执行以下操作


选择ROW_NUMBER()超过(ORDER BY名称)AS行号，
*来自myTable
其中名称=``艾伦''

带有示例代码的任何想法都是很棒的

I want a value from a previous row.

myTable with values

RowNo Name Salary
1 Alan 20000
2 Alan 22000
3 Alan 24000

I want to get the previous value from the last row e.g. 22000

I do not have a row number in my table so done the following


select ROW_NUMBER() over (ORDER BY Name) AS RowNo,
* from myTable
where Name = ''Alan''

Any ideas with sample code would be fantastic

推荐答案

这应该做到的

This should do it

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY Name, RowNo),
    Salary, RowNo, Name
  FROM myTable
)
SELECT
  cur.RowNo, cur.Name, cur.Salary, ISNULL(prev.Salary,0)
FROM CTE cur
LEFT OUTER JOIN CTE prev on prev.rownum = cur.rownum - 1

您说表中没有行号，但是您的数据显示的是RowNo ...没关系，但是您可能想更改ORDER BY子句.编辑-重新阅读您的问题-对我对RowNo感到困惑的歉意-忽略我:-)

这将产生以下结果:

You said there was no row number in your table but your data showed a RowNo... it doesn''t really matter but you might want to change the ORDER BY clause. Edit - just re-read your question - apologies about my confusion on RowNo - ignore me :-)

This produces the following results:

ROWNO NAME SALARY COLUMN_3
1     Alan 20000  0 
2     Alan 22000  20000 
3     Alan 24000  22000 

这篇关于如何从上一行获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！