如何通过提供路径来查询相邻关系(在SQL中)以获取叶?

在CTE的帮助下:

WITH CategoriesWithPath (id, parentid, name, path) AS
(
    SELECT  c.*, cast(c.name AS VARCHAR(1024)) AS "path"
    FROM    @Categories c
    WHERE   parentid is null
    UNION ALL
    SELECT  c.*, cast(p."path" + '/' + c.name AS VARCHAR(1024)) AS "path"
    FROM    @Categories c
    INNER JOIN CategoriesWithPath p ON c.parentid = p.id
)
SELECT  id, parentid, name 
FROM    CategoriesWithPath
WHERE   "path" = 'a/b/c/d'

但是我会使用这个CTA创建一个视图,这样您就可以轻松地执行 SELECT 基于“路径”过滤器的视图。

下面是使用公共表表达式(CTE)的SQL Server(2005+)方法:

declare @leaf varchar(10)
declare @fullpath varchar(50)

set @leaf = 'D'
set @fullpath = '/A/B/C/D'

with p as
(
    select
        *,
        '/' + name as path
    from
        categories 
    where 
        leaf = @leaf
    union all
    select
        c.*,
        '/' + name + p.path as path
    from
        categories c
        inner join p on
            c.id = p.parentid
)

select
    *
from
    p
where
    path = @fullpath

虽然没有直接回答您的问题,但如果您有权访问SQL Server 2008,则可能需要考虑使用 Hierarchid data type .

我不认为这样做的意义,因为你已经建立了道路,知道“D”是什么。您不能只将路径拆分为'\'并使用最后一个查询项。