在SQL 2005中从递归查询结果中排序层次结构

Question

我有一个“任务”表，其中包含以下列（TaskOrder用于在父级范围内对子级进行排序，而不是整个表）：

TaskId
ParentTaskId
TaskName
TaskOrder

我有这个CTE查询来返回所有行：

with tasks (TaskId, ParentTaskId, [Name]) as
(
    select parentTasks.TaskId,
           parentTasks.ParentTaskId,
           parentTasks.[Name]
    from   Task parentTasks
    where  ParentTaskId is null

    union all

    select childTasks.TaskId,
           childTasks.ParentTaskId,
           childTasks.[Name]
    from   Task childTasks
    join   tasks
    on     childTasks.ParentTaskId = tasks.TaskId
)

select * from tasks

这个查询按照任务级别排序返回所有任务，但是我该如何将结果按照以下层次结构排序呢？

- 任务1
-- 任务1 子任务1
-- 任务1 子任务2
- 任务2
- 任务3

谢谢。 编辑：答案应该适用于无限层级。

- Nick Crowther

---

你只是试验公用表达式（CTEs），还是这比直接使用SQL更简单呢？ - dkretz

是的，我正在尝试使用CTE，但如果有更好的直接SQL解决方案，我会使用它。 - Nick

Answer 1

您可以通过添加一个包含之前所有ID的列表的层次结构列来实现此目的:

with tasks (TaskId, ParentTaskId, [Name], TaskIdList) as
(
    select parentTasks.TaskId,
           parentTasks.ParentTaskId,
           parentTasks.[Name],
           parentTasks.TaskId
    from   Task parentTasks
    where  ParentTaskId is null

    union all

    select childTasks.TaskId,
           childTasks.ParentTaskId,
           childTasks.[Name],
           tasks.TaskIdList + '.' + childTasks.TaskId
    from   Task childTasks
    join   tasks
    on     childTasks.ParentTaskId = tasks.TaskId
)

select TaskId, ParentTaskId, [Name] from tasks
   order by TaskIdList

请注意，这里假定TaskId是基于字符串的ID。如果不是，则在连接之前应将其转换为varchar类型。

- Mark Westley

---

谢谢，希望能找到一个不需要额外列的解决方案；当子任务在父任务之间移动时，这会使维护变得更加困难。 - Nick

Answer 2

使用 Mark's method 的变体解决了问题，但我没有在每个节点中保留节点路径，这样我可以更轻松地将它们移动到树中。相反，我将我的 'OrderBy' 列从 int 类型更改为左填充了零的 varchar(3) 类型，以便可以将它们连接成返回的所有行的主要 'OrderBy'。

with tasks (TaskId, ParentTaskId, OrderBy, [Name], RowOrder) as
(
    select  parentTasks.TaskId,
            parentTasks.ParentTaskId,
            parentTasks.OrderBy,
            parentTasks.[Name],
            cast(parentTasks.OrderBy as varchar(30)) 'RowOrder'
    from    Task parentTasks
    where   ParentTaskId is null

    union all

    select  childTasks.TaskId,
            childTasks.ParentTaskId,
            childTasks.OrderBy,
            childTasks.[Name],
            cast(tasks.RowOrder + childTasks.OrderBy as varchar(30)) 'RowOrder'
    from    Task childTasks
    join    tasks
    on      childTasks.ParentTaskId = tasks.TaskId
)

select * from tasks order by RowOrder

这个返回：

TaskId ParentTaskId OrderBy Name RowOrder --------------------------------------------------------------------------- 1 NULL 001 任务一 001 15 1 001 任务一 / 任务一 001001 2 NULL 002 任务二 002 7 2 001 任务二 / 任务一 002001 14 7 001 任务二 / 任务一 / 任务一 002001001 8 2 002 任务二 / 任务二 002002 9 8 001 任务二 / 任务二 / 任务一 002002001 10 8 002 任务二 / 任务二 / 任务二 002002002 11 8 003 任务二 / 任务二 / 任务三 002002003 3 NULL 003 任务三 003 4 NULL 004 任务四 004 13 4 001 任务四 / 任务一 004001 5 NULL 005 任务五 005 6 NULL 006 任务六 006 17 NULL 007 任务七 007 18 NULL 008 任务八 008 19 NULL 009 任务九 009 21 19 001 任务九 / 任务一 009001 20 NULL 010 任务十 010

它不允许无限层级（最多10个级别/每个父节点最多1000个子节点-如果我从0开始排序），但对我的需求已经足够了。

- Nick Crowther

Answer 3

你不需要所有的联合体，我认为这应该可以工作：

select
 TaskId,
 ParentTaskId,
 [Name],
 COALESCE(ParentTaskId, TaskId) as groupField
from
 task
order by
 COALESCE(ParentTaskId, TaskId), ParentTaskId, TaskId

- WildJoe

Answer 4

由于您没有指定“ORDER BY”，那么您希望它以任何特定顺序返回它们呢？（除非希望查询分析器以某种预期的方式工作）。

如果您想按ParentTaskId，TaskId的顺序排列，则在第一个UNION元素中选择TaskId作为ParentTaskId和NULL作为TaskId; 然后

按ParentTaskId，TaskId排序？

- le dorfier

---

正确，漏掉了一个重要的部分！我还有一个“order by”列，用于对父级内的子级进行排序。 - Nick