排序分层表CTE查询

Question

如何使用CTE查询对分层表进行排序？

示例表：

|ID|Name  |ParentID|
| 0|      |-1      |
| 1|1     |0       |
| 2|2     |0       |
| 3|1-1   |1       |
| 4|1-2   |1       |
| 5|2-1   |2       |
| 6|2-2   |2       |
| 7|2-1-1 |5       |

我最喜欢的结果是：

|ID|Name  |ParentID|Level
| 0|      |-1      |0
| 1|1     |0       |1
| 3|1-1   |1       |2
| 4|1-2   |1       |2
| 2|2     |0       |1
| 5|2-1   |2       |2
| 7|2-1-1 |5       |3
| 6|2-2   |2       |2

另一个示例：

另一个示例：

|ID|Name      |ParentID|
| 0|          |-1      |
| 1|Book      |0       |
| 2|App       |0       |
| 3|C#        |1       |
| 4|VB.NET    |1       |
| 5|Office    |2       |
| 6|PhotoShop |2       |
| 7|Word      |5       |

我最喜欢的结果是：

|ID|Name      |ParentID|Level
| 0|          |-1      |0
| 1|Book      |0       |1
| 3|C#        |1       |2
| 4|VB.NET    |1       |2
| 2|App       |0       |1
| 5|Office    |2       |2
| 7|Word      |5       |3
| 6|PhotoShop |2       |2

Answer 1

hierarchyid数据类型能够表示分层数据，并且已经具有所需的排序顺序。如果你不能替换你的ParentID列，那么你可以动态地转换成它：

(此脚本的大部分是数据设置，实际答案很小)

declare @t table (ID int not null,Name varchar(10) not null,ParentID int not null)
insert into @t(ID,Name,ParentID)
select 0,''          ,-1 union all
select 1,'Book'      ,0  union all
select 2,'App'       ,0  union all
select 3,'C#'        ,1  union all
select 4,'VB.NET'    ,1  union all
select 5,'Office'    ,2  union all
select 6,'PhotoShop' ,2  union all
select 7,'Word'      ,5

;With Sensible as (
    select ID,Name,NULLIF(ParentID,-1) as ParentID
    from @t
), Paths as (
    select ID,CONVERT(hierarchyid,'/' + CONVERT(varchar(10),ID) + '/') as Pth
    from Sensible where ParentID is null
    union all
    select s.ID,CONVERT(hierarchyid,p.Pth.ToString() + CONVERT(varchar(10),s.ID) + '/')
    from Sensible s inner join Paths p on s.ParentID = p.ID
)
select
    *
from
    Sensible s
        inner join
    Paths p
        on
            s.ID = p.ID
order by p.Pth

Answer 2

ORDER BY Name应按预期工作：

WITH CTE 
AS(
    SELECT parent.*, 0 AS Level
    FROM @table parent
    WHERE parent.ID = 0

    UNION ALL

    SELECT parent.*, Level+1
    FROM @table parent
    INNER JOIN CTE prev ON parent.ParentID = prev.ID
)

SELECT * FROM CTE 
ORDER BY Name

这里是你的样本数据(下次自己添加)：

declare @table table(ID int,Name varchar(10),ParentID int);
insert into @table values(0,'',-1);
insert into @table values(1,'1',0);
insert into @table values(2,'2',0);
insert into @table values(3,'1-1',1);
insert into @table values(4,'1-2',1);
insert into @table values(5,'2-1',2);
insert into @table values(6,'2-2',2);
insert into @table values(7,'2-1-1',5);

结果：

ID  Name    ParentID    Level
0             -1          0
1    1         0          1
3    1-1       1          2
4    1-2       1          2
2    2         0          1
5    2-1       2          2
7    2-1-1     5          3
6    2-2       2          2