树形表菜单的 SQL 查询

Question

我有一个树状结构的表:

ID    Title           ParentID     Orderby 
----------------------------------------
1     All             0            2
2     Banking         1            5
3     USAA Checking   0            0
4     USAA Mastercard 1            9
5     Medical         3            0
6     Jobs            3            100
7     Archive         0            1
8     Active          7            0  
9     BoA Amex        1            1

我需要编写一个 SQL 查询来产生这样的结果(按列 Orderby 进行排序):

ID    Title           Path         Orderby 
----------------------------------------
3     USAA Checking   1            0
5     Medical         1.1          0
6     Jobs            3.2          100
7     Archive         2            1
8     Active          2.1          0
1     All             3            2
9     BoA Amex        3.1          1
2     Banking         3.2          5
4     USAA Mastercard 3.3          9

谁能帮我写一个SQL查询？谢谢!

Answer 1

我们可以使用递归 CTE 来做到这一点:

WITH children AS (
    SELECT NULL AS ParentID, ID, Title, Orderby,
        CAST(ID AS VARCHAR(500)) AS Path
    FROM Categories
    WHERE ParentID = 0

    UNION ALL

    SELECT 
       d.ParentID, t.counter + 1, d.ID, d.Title, d.Orderby,
       CAST(CAST(t.Path AS VARCHAR(50)) + '.' +
       CAST(ROW_NUMBER() OVER (PARTITION BY d.ParentID ORDER BY d.ID) AS VARCHAR(50)) AS VARCHAR(500))
    FROM children t
    INNER JOIN Categories AS d
        ON d.ParentID = t.ID
)

SELECT ID, Title, Path, Orderby
FROM children;

Demo

请注意，对于给定的父版本，您从未提供用于确定次要版本号的固定逻辑。也就是说，不清楚为什么 Medical 在层次结构中出现在 Jobs 之前。