SQL 递归计算日期

Question

我有一个 sql server 表:

CREATE TABLE [Workflow].[MilestoneDate](
 [MilestoneDateId] [int] IDENTITY(1,1) NOT NULL,
 [SpecifiedDate] [datetime] NULL,
 [RelativeDays] [int] NULL,
 [RelativeMilestoneDateId] [int] NULL,
 CONSTRAINT [PK_MilestoneDate] PRIMARY KEY CLUSTERED 
(
 [MilestoneDateId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Workflow].[MilestoneDate]  WITH CHECK ADD  CONSTRAINT [FK_MilestoneDate_MilestoneDate] FOREIGN KEY([RelativeMilestoneDateId])
REFERENCES [Workflow].[MilestoneDate] ([MilestoneDateId])
GO
ALTER TABLE [Workflow].[MilestoneDate] CHECK CONSTRAINT [FK_MilestoneDate_MilestoneDate]

它的数据可能如下所示:

Id     Date                          RelDays RelId
49     2010-03-04 00:00:00.000       NULL    NULL
746    NULL                          6       46
747    NULL                          20      746
46     2010-02-18 00:00:00.000       NULL    NULL
48     2010-04-04 00:00:00.000       NULL    NULL
47     2010-05-04 00:00:00.000       NULL    NULL
748    NULL                          14      48

我需要做的是获取每一行的计算日期，如果有的话是日期，或者是“父”项的日期(使用 RelId)加上 RelDays(可以是递归的)。

例如，ID 747 的计算日期为 20 天 + 6 天 + 2010-02-18，因此为 2010-03-16。

Answer 1

试试这个(使用仅在 SQL Server 2005 及更高版本上可用的 CTE):

DECLARE @YourTable table (Id int, Date datetime, RelDays int, RelId int)
INSERT @YourTable VALUES (49 ,'2010-03-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (746, NULL                    ,6    ,46)
INSERT @YourTable VALUES (747, NULL                    ,20   ,746)
INSERT @YourTable VALUES (46 ,'2010-02-18 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (48 ,'2010-04-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (47 ,'2010-05-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (748, NULL                    ,14   ,48)

;WITH R AS
(
    SELECT id,Date,RelDays,RelId  --get all parents
    FROM @YourTable
    WHERE RelId IS NULL
    UNION ALL    --recursive go through all children, adding the days on
    SELECT
        y.id,r.Date+y.RelDays,y.RelDays,y.RelId
        FROM @YourTable y
            INNER JOIN R ON y.RelId=r.Id
)
select * from R

输出:

id          Date                    RelDays     RelId
----------- ----------------------- ----------- -----------
49          2010-03-04 00:00:00.000 NULL        NULL
46          2010-02-18 00:00:00.000 NULL        NULL
48          2010-04-04 00:00:00.000 NULL        NULL
47          2010-05-04 00:00:00.000 NULL        NULL
748         2010-04-18 00:00:00.000 14          48
746         2010-02-24 00:00:00.000 6           46
747         2010-03-16 00:00:00.000 20          746

(7 row(s) affected)