sql - 递归 CTE 查询生成产品或页面层次结构

Question

Page_ID     Page_Name               Page_Title              Page_Handler        Parent_Page_ID  Language_ID     Region_ID
1           Home                    Home                    index.aspx              0               1               uae
2           Personal                Personal                index.aspx              0               1               uae
3           Accounts & Deposits     Accounts & Deposits     index.aspx              2               1               uae
4           Current Account         Current Account         current-account.aspx    3               1               uae
5           Current Gold Accounts   gold Account            gold-account.aspx       3               1               uae
6           Easy Saver              Easy Saver Account      saver-account.aspx      3               1               uae
7           Fixed Deposits          Fixed Account           fixed-account.aspx      3               1               uae
8           Loans                   Loans                   index.aspx              2               1               uae
9           Personal Loans          Personal Loans          index.aspx              8               1               uae
10          car Loans               car Loans               car-loan.aspx           8               1               uae

我想为 ASP.NET 路由生成以下路径，我也可以将其用于面包屑导航

www.abc.com/home
www.abc.com/personal
www.abc.com/personal/acounts-deposits/
www.abc.com/personal/acounts-deposits/current-account
www.abc.com/personal/acounts-deposits/current-gold-account
www.abc.com/personal/acounts-deposits/easy-saver
......
......
www.abc.com/personal/loans/
www.abc.com/personal/loans/personal-loans
www.abc.com/personal/loans/car-loans

我修改了 CTE 脚本以使用类似的表结构。但这个 CTE 的问题是，它给我作为 sibling/child/parent... 而我需要路径作为 parent/child/sibling

示例 http://sqlfiddle.com/#!3/0d086/1

示例代码 - 由于 sqlfiddle 的限制，我使用了短数据

CREATE TABLE PageMenu
    ([PageId] int, [PageName] varchar(5), [PageInheritance] int)
;

INSERT INTO PageMenu
    ([PageId], [PageName], [PageInheritance])
VALUES
    (1, 'home', 0),
    (2, 'p1', 0),
    (3, 'c1', 2),
    (4, 'c2', 2),
    (5, 's3', 4),
    (6, 'S3a', 5)
;
WITH CategoryCTE AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            RecursionLevel = 1, 
            ParentRoot = CAST('None' AS VARCHAR(MAX)),
            LastParentCatID = PageInheritance
    FROM    PageMenu
    UNION ALL
    SELECT  cte.PageId, 
            cte.PageName,
            cte.PageInheritance,
            cte.RecursionLevel + 1,
            ParentRoot = CASE WHEN cte.RecursionLevel = 1 THEN '' ELSE cte.ParentRoot + '/' END + c.PageName,
            LastParentCatID = c.PageInheritance
    FROM    CategoryCTE cte
            INNER JOIN PageMenu c
                ON c.PageId = cte.LastParentCatID
), MaxRecursion AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            ParentRoot, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY PageId ORDER BY RecursionLevel DESC)
    FROM    CategoryCTE
)
SELECT  PageId, PageName, PageInheritance, ParentRoot
FROM    MaxRecursion
WHERE   RowNum = 1 ORDER BY PageId DESC;

Answer 1

我认为M. Ali(在评论中进行了更正)比您的方法更合适。无论如何，如果您只想更正查询，只需将递归查询中的 ParentRoot 计算更改为:

ParentRoot = CASE WHEN cte.ParentRoot <> 'None' THEN c.PageName + '/' + cte.ParentRoot ELSE c.PageName END,

因此，您的查询将如下所示:

    WITH CategoryCTE AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            RecursionLevel = 1, 
            ParentRoot = CAST('None' AS VARCHAR(MAX)),
            LastParentCatID = PageInheritance
    FROM    PageMenu
    UNION ALL
    SELECT  cte.PageId, 
            cte.PageName,
            cte.PageInheritance,
            cte.RecursionLevel + 1,
            ParentRoot = CASE WHEN cte.ParentRoot <> 'None' THEN c.PageName + '/' + cte.ParentRoot ELSE c.PageName END,
            LastParentCatID = c.PageInheritance
    FROM    CategoryCTE cte
            INNER JOIN PageMenu c
                ON c.PageId = cte.LastParentCatID
), MaxRecursion AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            ParentRoot, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY PageId ORDER BY RecursionLevel DESC)
    FROM    CategoryCTE
)
SELECT  PageId, PageName, PageInheritance, ParentRoot
FROM    MaxRecursion
WHERE   RowNum = 1 
ORDER BY PageId DESC;

更新:这是阿里的答案，已更正以显示问题作者想要的结果，甚至进行了简化:

;with cte 
as (
    select
    t.PageInheritance, 1 as Level, 
    cast(t.Pagename as nvarchar(max)) AS [Path]
    from PageMenu as t

    union all

    select
    t.PageInheritance, Level + 1 as Level,
    cast(t.PageName as nvarchar(max)) + '/' + c.[Path] as [Path] 
    from PageMenu as t  
    inner join cte as c on c.PageInheritance = t.PageId
)
select N'www.abc.com/' + [Path] AS [Paths]
from cte 
WHERE PageInheritance = 0
order by [Level] ASC