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

此查询返回正如您所期望的，所有任务均按其级别排序。 我如何更改它以将结果按其层次结构顺序排序，如下所示？

- Task 1
-- Task 1 Subtask 1
-- Task 1 Subtask 2
- Task 2
- Task 3

谢谢。

编辑：答案应该适用于无限数量的级别。

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。

Answer 2

使用Mark方法的变体解决了该问题，但我没有保留每个节点中的节点路径，因此我可以更轻松地在树中移动它们。 相反，我将“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      Task One                          001
15      1             001      Task One / Task One               001001
2       NULL          002      Task Two                          002
7       2             001      Task Two / Task One               002001
14      7             001      Task Two / Task One / Task One    002001001
8       2             002      Task Two / Task Two               002002
9       8             001      Task Two / Task Two / Task One    002002001
10      8             002      Task Two / Task Two / Task Two    002002002
11      8             003      Task Two / Task Two / Task Three  002002003
3       NULL          003      Task Three                        003
4       NULL          004      Task Four                         004
13      4             001      Task Four / Task One              004001
5       NULL          005      Task Five                         005
6       NULL          006      Task Six                          006    
17      NULL          007      Task Seven                        007
18      NULL          008      Task Eight                        008
19      NULL          009      Task Nine                         009
21      19            001      Task Nine / Task One              009001
20      NULL          010      Task Ten                          010

它不允许无限的层次结构（每个父节点最多 10 个级别/最多 1000 个子节点 - 如果我从 0 开始 OrderBy），但足以满足我的需求。

Answer 3

你不需要所有工会的东西，我认为这应该有效：

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

Answer 4

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

如果您希望按 ParentTaskId、TaskId 顺序排列，则在第一个 UNION 元素中选择 TaskId 作为 ParentTaskId，并选择 NULL 作为 TaskId； 那么

ORDER BY ParentTaskId, TaskId?