代码之家 › 专栏 › 技术社区 › Mickel

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

linq-to-sql sql-server mysql sql

Mickel · 技术社区 · 15 年前

你好,各位编码员!

我有一个包含类别和子类别的表(实际上我没有,但是让我们坚持一个经典的例子):

Id     ParentId     Name
1      NULL         A
2      1            B
3      2            C
4      3            D

5      NULL         B
6      5            D

有没有一种方法可以通过查询表中的完整路径来获取类别“D”(ID 4)?(见下面的伪代码)

SELECT * FROM Categories WHERE FullPath = "A/B/C/D"

// Result:
Id     ParentId     Name
4      3            D

我知道可以使用左联接来获取完整路径,但是如何通过提供路径来编写查询来获取叶节点?

编辑(解决方案):

帮助范和埃里克,这就是我所做的:

with p as
(
    select
        c.*,
        cast(c.Name as varchar(1024)) as NamePath
    from
        Categories c
    where 
        ParentCategoryId is null
    union all
    select
        c.*,
        cast(p.NamePath + '/' + c.Name as varchar(1024)) as NamePath
    from
        Categories c
        inner join p on
            c.ParentCategoryId = p.CategoryId
)
select Id, Name
from p
where NamePath = 'A/B/C/D'

谢谢各位,你们的回答都很有帮助!我希望我能把它们都标记为解决方案。

这一次,我只想选一辆有最多积分的车(那就是货车)。

4 回复 | 直到 15 年前

van 15 年前

在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 基于“路径”过滤器的视图。

Eric 15 年前

下面是使用公共表表达式(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

John Sansom 15 年前

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

skyfoot 15 年前

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