主页 / dba / 问题 / 283936
Accepted
Elio Fernandes
Asked: 2021-01-26 07:40:45 +0800 CST

根据先前记录中另一列的日期将日期增加 1 天

  • 772

我使用的是SQL Server 2008 R2，我没有 LAG 和 LEAD 功能。

我有这个初始表，我需要 StartDate 等于上一条记录的 EndDate 加上 1 天。见结果表。

在此处输入图像描述

问候， 埃利奥·费尔南德斯

sql-server-2008-r2 date

1 个回答

  1. Best Answer

    试试这个自我加入：

    CREATE TABLE [dbo].[Table_1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date] [datetime2](5) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
) ON [PRIMARY]

    一些数据：

    SET IDENTITY_INSERT [dbo].[Table_1] ON 
INSERT [dbo].[Table_1] ([id], [date]) VALUES (1, CAST(N'2021-01-01T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (2, CAST(N'2021-01-03T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (3, CAST(N'2021-01-06T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (4, CAST(N'2021-01-10T00:00:00.0000000' AS     DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (5, CAST(N'2021-01-20T00:00:00.0000000' AS DateTime2))
SET IDENTITY_INSERT [dbo].[Table_1] OFF

    这个查询：

    select t.id,t.date as [from], tnext.date as [to]
from table_1 as t left join
     table_1 as tnext
     on t.id = tnext.id -1
     order by 1

    在此处输入图像描述

    • 1

相关问题