mysql - 层次表-如何获取项目的路径[MySQL中的链表]

Question

我在 MySQL 中有一个分层表:每个项目的 parent 字段指向其父项目的 id 字段。对于每个项目，我都可以使用 query described here 获取其所有父项的列表 [无论深度如何] .使用 GROUP_CONCAT 我将完整路径作为单个字符串获取:

SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM (
SELECT  @r AS _id,
         (
         SELECT  @r := parent
         FROM    t_hierarchy
         WHERE   id = _id
         ) AS parent,
         @l := @l + 1 AS lvl
 FROM    (
         SELECT  @r := 200,
                 @l := 0
         ) vars,
         t_hierarchy h
WHERE    @r <> 0
ORDER BY lvl DESC
) x

只有当项目的 id 是固定的 [在本例中为 200] 时，我才能完成这项工作。

我想对所有行执行相同的操作:使用一个附加字段 (path) 检索整个表，该字段将显示完整路径。我想到的唯一解决方案是将此查询包装在另一个选择中，设置一个临时变量 @id 并在子查询中使用它。但它不起作用。我在 path 字段中得到了 NULL。

SELECT @id := id, parent, (
    SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM (
    SELECT  @r AS _id,
             (
             SELECT  @r := parent
             FROM    t_hierarchy
             WHERE   id = _id
             ) AS parent,
             @l := @l + 1 AS lvl
     FROM    (
             SELECT  @r := @id,
                     @l := 0
             ) vars,
             t_hierarchy h
    WHERE    @r <> 0
    ORDER BY lvl DESC
    ) x
) as path
 FROM t_hierarchy

P.S. 我知道我可以将路径存储在单独的字段中并在插入/更新时更新它们，但我需要一个基于链表技术的解决方案。

更新:我希望看到一个不使用递归或结构的解决方案，例如 for 和 while。上述查找路径的方法不使用任何循环或函数。我想找到相同逻辑的解决方案。或者，如果不可能，请尝试解释原因!

Answer 1

定义 getPath 函数并运行以下查询:

select id, parent, dbo.getPath(id) as path from t_hierarchy 

定义 getPath 函数:

create function dbo.getPath( @id int)
returns varchar(400)
as
begin
declare @path varchar(400)
declare @term int
declare @parent varchar(100)
set @path = ''
set @term = 0
while ( @term <> 1 )
begin
   select @parent = parent from t_hierarchy where id = @id
   if ( @parent is null or @parent = '' or  @parent = @id )
        set @term = 1
   else
        set @path = @path + @parent   
   set @id = @parent     
end
return @path
end