sql-server - SqlServer 分层父/子查询，父级内部有子级排序

Question

我有一个包含这样数据的表格-

ID  ParentID    ProductTypeName
1   NULL        Electronics
200 1           TV
300 200         Plasma
67  NULL        KitchenAppliances
78  67          Stoves
82  78          Electric
99  78          Gas
23  200         LED
65  300         LG
66  300         Sony

我想获取以下格式的数据 -

ID  ParentID    ProductTypeName         Level   Sort(Or Some kind of sort value)
1   NULL        Electronics             0       1
200 1           TV                      1       110
300 200         LED                     2       120
65  300         LG                      3       12010
66  300         Sony                    3       12020
23  200         Plasma                  2       100030
67  NULL        KitchenAppliances       0       10000010
78  67          Stoves                  1       1000001010
82  78          Electric                2       100000101020
99  78          Gas                     2       100000101030

以此格式在树中显示数据。请注意，这里每个父级中的子级也已排序。缩进是为了更好地了解结果 -

Electronics
    TV
        LED
            LG
            Sony
        Plasma
KitchenAppliances
    Stoves
        Electric
        Gas

这是我编写的查询，但似乎不起作用。排序号逻辑似乎被破坏了。有人可以帮忙解决这个问题吗？任何帮助表示赞赏。谢谢。

;WITH cte (ID, ParentID, [Level], [Name], Sort) AS(
                        SELECT      sc1.ID,
                                    NULL,
                                    0,
                                    sc1.Name,
                                    cast(row_number()over(partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max)) as Sort
                        FROM  TableData sc1
                        WHERE sc1.ID is null 
                        UNION ALL
                        SELECT      sc2.ID,
                                    sc2.ParentID,
                                    g2.[level] + 1,
                                    sc2.Name, 
                                    g2.Sort + cast(row_number()over(partition by sc2.ParentCategoryID order by sc2.Name) as varchar(max))Sort
                        FROM  dbo.TableData sc2
                              INNER JOIN cte g2
                                          ON    sc2.ParentID = g2.ID

Answer 1

你做得几乎是正确的。我唯一改变的是:cte的非递归部分中的条件WHERE sc1.ID is null更改为WHERE sc1.ParentID is null，以及排序键的方式(路径)计算:

;WITH cte (ID, ParentID, [Name], Level, SortPath, DisplayPath)
AS(
    SELECT sc1.ID, NULL, sc1.Name, 0,
        cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varbinary(max)),
        cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max))
    FROM dbo.TableData sc1
    WHERE sc1.ParentID is null 
    UNION ALL
    SELECT sc2.ID, sc2.ParentID, sc2.Name, g2.Level + 1
        g2.SortPath + cast(row_number() over (partition by sc2.ParentCategoryID order by sc2.Name) as binary(4)),
        g2.DisplayPath + '.' + cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(10))
    FROM dbo.TableData sc2
        JOIN cte g2 ON sc2.ParentID = g2.ID
)
select ID, ParentID, Name, DisplayPath
from cte
order by SortPath

可以看到，计算了两条路径，第一个用于元素排序，第二个用于查看。如果树元素的路径不应该显示在任何地方，您可以只保留 SortPath。