代码之家 › 专栏 › 技术社区 › Ocelot20

递归SQL以查找关键路径?

recursion tsql sql-server-2005 sql

Ocelot20 · 技术社区 · 14 年前

考虑到这两个表:

[dbo].[Task]
[Id]   [Duration]   [ScheduledStart]
int    int          Nullable DateTime

[dbo].[TaskDependencies]
[Id]   [PredecessorTaskId]   [TaskId]
int    FK_Task_Id            FK_Task_Id

我正在查找任务的最新前一个任务的结束日期。对于任务表,scheduledstart可以为空。其背后的想法是,如果没有显式计划的开始,则可以从其前置任务派生(根级任务必须具有计划的开始,因此计算可以从某个地方开始)。任务也可以有多个前置任务。

我想出了一个假递归函数,它(我认为)完成了我正在寻找的工作。我想知道的是,既然我已经习惯了更程序化的编程,那么是否有一种更有效的方法来用SQL编写它。我应该让函数\成为一个存储过程并让它自己调用吗?有没有一种方法可以通过WITH语句来实现这一点(这是一种更有效的递归查询方法,还是像这样的递归函数)?

DateTime Function_A(Task)
{
    var predecessorList = getPredecessors(Task)
    var latestEndDate;
    var currentPredecessorLatestEndDate;

    ForEach(Predecessor in predecessorList)
    {
        if(Predecessor.ScheduledStart != null)
        {
            if(latestEndDate != null)
            {
                if(Predecessor.StartDate + Predecessor.Duration > latestEndDate)
                {
                    latestEndDate = Predecessor.StartDate + Predecessor.Duration;
                }
            }
            else
            {
                latestEndDate = Predecessor.StartDate + Predecessor.Duration;
            }

        }
        else
        {
            currentPredecessorLatestEndDate = Function_A(Predecessor.Id);

            if(latestEndDate != null)
            {
                if(currentPredecessorEndDate > latestEndDate)
                {
                    latestEndDate = currentPredecessorEndDate;
                }
            }
            else
            {
                latestEndDate = currentPredecessorEndDate;              
            }
        }
    }

    return latestEndDate;
}

谢谢你的帮助!

1 回复 | 直到 14 年前

Andomar 14 年前

;with Predecessors as
(
select  Id as RootId
,       null as ChildId
from    @Task
union all
select  p.RootId
,       cd.PredecessorTaskId as ChildId
from    @TaskDependencies cd
join    Predecessors p
on      cd.TaskId = ISNULL(p.ChildId, p.RootId)
)
select  RootId
,       max(dateadd(day, c.Duration+1, c.ScheduledStart))
from    Predecessors p
join    @Task c
on      p.ChildId = c.Id
        -- Filter out tasks with child tasks that themselves have
        -- an unknown start date.
where   not exists
        (
        select  *
        from    Predecessors p2
        join    @Task c2
        on      p2.ChildId = c2.Id
        where   p2.RootId = p.RootId
                and c2.ScheduledStart is null
        )
group by
        RootId

declare @Task table (Id int, Duration int, ScheduledStart datetime)
insert @Task 
          select 1, 3, '2010-01-01'
union all select 2, 3, '2010-01-03'
union all select 3, 3, null
union all select 4, 3, '2010-01-01'
union all select 5, 3, null

declare @TaskDependencies table (PredecessorTaskId int, TaskId int)
insert @TaskDependencies
          select 1, 3
union all select 2, 3
union all select 4, 5
union all select 3, 5

3    2010-01-07