sql-server - 用于递归和排序的 SQL Server CTE

Question

我在 SQL Server (2012) 中有下表:

我的表 :

Id      __ParentId      Priority
1       NULL            NULL    
2       1               100     
3       1               300     
4       1               200     
5       4               100     
6       4               200     
7       6               100     
8       5               100     
9       5               200     
10      9               100     
11      5               50      

__ParentId 列引用了 身份证 知道任何一行的父级，它可以向下递归到多个级别(例如，Id 8 是 5 的子级，它是 4 的子级，它是 1 的子级) .

此外，还有一个 优先列显示子项应出现在父项中的顺序(优先级最低的数字)。

所以，我想得到的决赛 table 是:

Id      __ParentId  Priority    Order   
1       NULL        NULL        1       
2       1           100         2       
4       1           200         3       
5       4           100         4       
11      5           50          5       
8       5           100         6       
9       5           200         7       
10      9           100         8       
6       4           200         9       
7       6           100         10      
3       1           300         11      

为了解释触摸，我们有 2是 1 的 child 并且具有最高优先级，但没有 child ，所以我们停在那里，然后 4是下一个优先级 child ，所以它是下一个，但是我们根据优先级和等级划分为它的 child 和他们的 child 。

或者，通过树结构来解释:

 1
    2
    4
        5
            11
            8
            9
                10
        6
            7
    3

我可以创建 CTE 来为我提供 parent 的 child ，但我想不出获得正确排序的好方法，因此甚至无法提供我一直在尝试的良好 SQL。

Answer 1

SQL2008+ :

尝试以下解决方案:

DECLARE @TableA TABLE (
    Id          INT NOT NULL PRIMARY KEY,
    __ParentId  INT NULL,
    [Priority]  INT NULL
);

INSERT @TableA (Id, __ParentId, [Priority])
VALUES 
(1 ,NULL,NULL),   
(2 ,1   ,100 ),   
(3 ,1   ,300 ),   
(4 ,1   ,200 ),   
(5 ,4   ,100 ),   
(6 ,4   ,200 ),   
(7 ,6   ,100 ),   
(8 ,5   ,100 ),   
(9 ,5   ,200 ),   
(10,9   ,100 ),   
(11,5   ,50  );

WITH CteRecursive
AS (
    SELECT  a.Id, a.__ParentId, a.[Priority], CONVERT(HIERARCHYID, '/' + LTRIM(a.Id) + '/') AS HID
    FROM    @TableA a
    WHERE   a.__ParentId IS NULL
    UNION ALL 
    SELECT  cld.Id, cld.__ParentId, cld.[Priority], CONVERT(HIERARCHYID, prt.HID.ToString() + LTRIM(cld.[Priority]) + '/') AS HID
    FROM     CteRecursive prt -- Parent
    JOIN @TableA cld ON prt.Id = cld.__ParentId -- Child
    WHERE   cld.__ParentId IS NOT NULL
)
SELECT *, r.HID.ToString() AS HIDToString FROM CteRecursive r
ORDER BY r.HID ASC

结果:



Demo

注意 #1:此解决方案使用 HIERARCHYID 的一个属性排序:HID 值使用深度优先方法排序(这意味着父级，然后是所有子级)。

Given two hierarchyid values a and b, a less than b means a comes before b in a depth-first traversal of the tree. Indexes on hierarchyid data types are in depth-first order, and nodes close to each other in a depth-first traversal are stored near each other. For example, the children of a record are stored adjacent to that record. For more information, see Hierarchical Data (SQL Server).

Reference