递归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的问题在于它给出了我需要的兄弟节点/子节点/父节点，而不是父节点/子节点/兄弟节点的路径。

示例 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;

- Learning

---

这个给定的测试数据的预期输出是什么？ - M.Ali

Answer 1

如果我理解正确，这将是我的解决方案，针对这个问题。它可能不是完整的答案，但它会让你接近目标。

;with cte 
as (
    select
        t.PageInheritance, 1 as Level,
        CASE WHEN cast(t.PageInheritance as nvarchar(max)) <> N'0' 
       THEN cast(t.PageInheritance as nvarchar(max)) + '/' + cast(t.PageId as nvarchar(max)) 
  ELSE cast(t.PageId as nvarchar(max)) END AS [Path]
    from PageMenu as t

    union all

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

结果集

╔═════════════════════╗
║        Paths        ║
╠═════════════════════╣
║ www.abc.com/1       ║
║ www.abc.com/2       ║
║ www.abc.com/2/4     ║
║ www.abc.com/2/3     ║
║ www.abc.com/2/4/5   ║
║ www.abc.com/2/4/5/6 ║
╚═════════════════════╝

- M.Ali

---

我会更改WHERE子句，删除不可搜索的LEFT函数，并将其改为WHERE PageInheritance = 0。 - Jaime

此外，在您进行编辑后，您还可以轻松地从CTE查询中删除结果集中的'0/'，从而避免复杂的字符串操作。就像这样： 在锚定查询中： CASE WHEN cast(t.PageInheritance as nvarchar(max)) <> N'0' THEN cast(t.PageInheritance as nvarchar(max)) + '/' + cast(t.PageId as nvarchar(max)) ELSE cast(t.PageId as nvarchar(max)) END AS [Path]在递归查询中： CASE WHEN cast(t.PageInheritance as nvarchar(max)) <> N'0' THEN cast(t.PageInheritance as nvarchar(max)) + '/' + c.[Path] ELSE c.[Path] END as [Path]如果我的评论对您有用，请给我点赞 :) - Jaime

非常感谢您的指导，它使得代码更简单、更高效。 :) - M.Ali

@M.Ali，如果您能将结果显示为“PageName”而不是“PageID”，我将不胜感激。我尝试改变了它，但最后出现了错误的结果，具体请参见http://sqlfiddle.com/#!3/0d086/22。 - Learning

@KnowledgeSeeker 只需在锚查询中两次将 cast(t.PageId as nvarchar(max)) 替换为 t.PageName。 - Jaime

@KnowledgeSeeker 你是对的。我已经检查过了，进行了修正和简化。我已经将它添加到我的答案中。希望这是你正确的答案。 - Jaime

Answer 2

我认为在评论中进行更正后的 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

- Jaime

---

@Jame，谢谢。看起来很好，我只是想通过添加过滤器“Where PageID=x”进一步增强这个CTE，以便我只能获取特定PageID的路径。 - Learning

@KnowledgeSeeker 你可以将该筛选条件添加到已有的筛选器中。像这样：WHERE RowNum = 1 AND PageId = X - Jaime

谢谢。我会将您的答案标记为正确，因为它更相关于我的问题。+1 给两个人。 - Learning