在SQL 2005中从递归查询结果排序层次结构 [英] Ordering hierarchy from recursive query results in SQL 2005

问题描述

我有一个包含以下各列的任务表（TaskOrder用于在父级范围内而不是整个表范围内排序子级）：

 
 TaskId 
 ParentTaskId 
 TaskName 
 TaskOrder 
 

我已经得到此CTE查询以返回所有行：

 ，任务（TaskId，ParentTaskId，[Name]）为
 （
 select parentTasks.TaskId，
 parentTasks.ParentTaskId，
 parentTasks。[Name] 
 from Task parentTasks 
其中ParentTaskId为空
 
合并所有
 
选择childTasks.TaskId，
 childTasks.ParentTaskId，
 childTasks。[名称] 
来自任务childTasks 
加入任务
 childTasks.ParentTaskId = task.TaskId 
）
 
 select *从任务
  

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

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

谢谢。 / p>

编辑：答案应该使用无数级。

解决方案

使用 Mark的方法，但是我并没有在每个节点中保留节点路径，因此我可以更轻松地在树上移动它们。取而代之的是，我将 OrderBy列从int更改为左侧用零填充的varchar（3），这样我就可以将所有返回的行将它们连接为主master'OrderBy'。

 ，任务（TaskId，ParentTaskId，OrderBy，[Name]，RowOrder）为
（
选择parentTasks.TaskId，
 parentTasks.ParentTaskId， 
 parentTasks.OrderBy，
 parentTasks。[名称]，
转换（parentTasks.OrderBy为varchar（30））'RowOrder'
来自任务parentTasks 
，其中ParentTaskId为null 
 
工会所有
 
选择childTasks.TaskId，
 childTasks.ParentTaskId，
 childTasks.OrderBy，
 childTasks。[Name]， 
 cast（tasks.RowOrder + childTasks.OrderBy as varchar（30））来自任务childTasks 
的'RowOrder'
加入childTasks.ParentTaskId = task.TaskId $ b $上的任务
 b）
 
选择*从RowOrder 
的任务订单中 

这将返回：

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

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

I've got a 'Task' table with the following columns (the TaskOrder is for ordering the children within the scope of the parent, not the entire table):

TaskId
ParentTaskId
TaskName
TaskOrder

I've got this CTE query to return all the rows:

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

This query returns all the tasks ordered by their level as you'd expect. How can I change it to order the results into their hierarchy order as below?

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

Thanks.

Edit: The answer should work with an unlimited numbr of levels.

解决方案

Solved the problem using a variation of Mark's method, but I'm not retaining the node path in every node, so I can more easily move them around the tree. Instead I changed my 'OrderBy' column from an int to varchar(3) left-padded with zeros so I can concatenate them into a master 'OrderBy' for all the rows returned.

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

This returns:

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

It doesn't allow for an unlimited hierarchy (max 10 levels / max 1000 children per parent node - if I'd started the OrderBy at 0) but more than enough for my needs.

这篇关于在SQL 2005中从递归查询结果排序层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！