SQL 在新列中使用以前的日期创建新列

问题描述

我想在 SQL Server(彩色编码)中得到以下结果

I would Like get below result in SQL Server (Color Coded)

所需的列是我希望日期的方式:

The Column Required is How I would like my Dates to be:

或者这个链接http://imgur.com/easxkMH

新日期必须特定于该 ID

The new dates have to be specific to that ID

我正在考虑为每个 ID 创建一个序列 (1, 2, 3,...) 编号的新列和另一列增加 1 (NULL, 1, 2, 3) 然后做一个自我左连接.

I was thinking of creating a new column of sequence(1, 2, 3,...) number per ID and another column where it is incremented by 1 (NULL, 1, 2, 3) and then doing a self left join.

请对任何其他程序提出建议并帮助代码

Please advice on any other procedure and help with the code

谢谢

推荐答案

可以使用 LEAD 和 LAG

语法

LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

SELECT    Id, Date
        , LEAD(Date) OVER (ORDER BY Id) AS [Next Date]
        , LAG(Date) OVER (ORDER BY Id) AS [Prev Date]
        , LEAD(Date, 2) OVER (ORDER BY Id) AS [2nd Next Date]
        , LAG(Date, 2) OVER (ORDER BY Id) AS [2nd Prev Date]
        , LEAD(Date, 2, 0) OVER (ORDER BY Id) AS [2nd Next Date]
        , LAG(Date, 2, 0) OVER (ORDER BY Id) AS [2nd Prev Date]
FROM    @Test_table

这篇关于SQL 在新列中使用以前的日期创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！