从下一行sql中的数据向列添加值

Base Table

基表

id line_number
1   1232
2   1456
3   1832
4   2002

I wish to add values to a new table such that the next row's value becomes the value in a new column with the last row's value being same..

我希望将值添加到新表中，以便下一行的值成为新列中的值，最后一行的值相同。

The final output I need to produce is:

我需要生成的最终输出是：

id line_number   end_line_number
1   1232         1456
2   1456         1832
3   1832         2002
4   2002         2002

The database is sql server.

数据库是sql server。

Any help is sincerely appreciated.

任何帮助都是真诚的感谢。

Thanks

谢谢

2 个解决方案

#1

After SQL Server 2012, you can use LEAD like this.

在SQL Server 2012之后，您可以像这样使用LEAD。

;WITH BaseTable as 
(
    SELECT 1 id,  1232 line_number
    UNION ALL SELECT 2 ,  1456
    UNION ALL SELECT 3,   1832
    UNION ALL SELECT 4 ,  2002
)
SELECT id,line_number,(LEAD(line_number,1,line_number) OVER(ORDER BY id ASC))
FROM BaseTable

For previous versions, try this

对于以前的版本，试试这个

;WITH BaseTable as 
(
    SELECT 1 id,  1232 line_number
    UNION ALL SELECT 2 ,  1456
    UNION ALL SELECT 3,   1832
    UNION ALL SELECT 4 ,  2002
), OrderedBaseTable as 
(
SELECT id,line_number,ROW_NUMBER() OVER(ORDER BY id asc) rw
FROM BaseTable
)
SELECT t1.id,t1.line_number,ISNULL(t2.line_number,t1.line_number) next_line_number
FROM OrderedBaseTable t1
LEFT JOIN OrderedBaseTable t2
ON t1.rw = t2.rw - 1

#2

Try this

尝试这个

 With T as (
    Select id, line_number, Row_Number() OVER(Order By id) + 1 As TempId From TableName)

    Select T1.id, T1.line_number, ISNULL(T2.line_number,T1.line_number) As end_line_number From T T1
    Left Join T T2 on T2.id = T1.TempId

SQL Fiddle Demo

SQL小提琴演示

#1