SQL 服务器 : get all parent child URL's from a single table

Question

我有一个看起来像这样的表:

Slug | Parent | MetaTitle | MetaDescription | MetaKeywords

网站上的 Url 是通过将 slug 附加到父级来构建的，只要父级不为空即可。所以如果我有这样的数据:

Slug            | Parent
-----------------------------------------
Home              null
About             null
Our-Company       About
Our-History       Our-Company

我希望能够运行一个可以帮助我的查询

/Home
/About
/About/Our-Company
/About/Our-Company/Our-History

这可能吗？任何帮助都会很棒!!!

Answer 1

可以使用 recursive CTE :

declare @URLTable table (
 Slug varchar(50),
 Parent varchar(50)
)

insert into @URLTable
 (Slug, Parent)
 values
 ('Home', null),
 ('About', null),
 ('Our-Company', 'About'),
 ('Our-History', 'Our-Company')


;with cteURLs as (
 select Slug, cast('/' + Slug as varchar(1000)) as URL
     from @URLTable
     where Parent is null
 union all
 select u.Slug, cast(c.URL + '/' + u.Slug as varchar(1000)) as URL
     from @URLTable u
         inner join cteURLs c
             on u.Parent = c.Slug
)
select URL from cteURLs

输出是:

URL
-----------------
/Home
/About
/About/Our-Company
/About/Our-Company/Our-History