Firebird 2.1层级表递归拼接节点名称并按字母排序的实现方法

方法一：递归存储过程实现 #

1. 创建存储过程 #

先创建两个存储过程，分别处理根节点和递归子节点：

-- 主存储过程：遍历根节点并触发子节点递归
CREATE PROCEDURE GetNodePaths
RETURNS (
    FullPath VARCHAR(1000),
    NodeID INTEGER
)
AS
DECLARE VARIABLE CurNodeID INTEGER;
DECLARE VARIABLE CurrentName VARCHAR(100);
BEGIN
    -- 按名称排序根节点
    FOR SELECT NodeID, Name 
        FROM Nodes 
        WHERE ParentNodeID = -1 
        ORDER BY Name 
        INTO :CurNodeID, :CurrentName DO
    BEGIN
        FullPath = :CurrentName;
        NodeID = :CurNodeID;
        SUSPEND;
        -- 递归处理当前根节点的子节点
        CALL GetChildPaths(:CurNodeID, :FullPath);
    END
END;

-- 子存储过程：递归处理子节点并拼接路径
CREATE PROCEDURE GetChildPaths(
    ParentID INTEGER,
    ParentPath VARCHAR(1000)
)
RETURNS (
    FullPath VARCHAR(1000),
    NodeID INTEGER
)
AS
DECLARE VARIABLE CurNodeID INTEGER;
DECLARE VARIABLE CurrentName VARCHAR(100);
BEGIN
    -- 按名称排序当前节点的子节点
    FOR SELECT NodeID, Name 
        FROM Nodes 
        WHERE ParentNodeID = :ParentID 
        ORDER BY Name 
        INTO :CurNodeID, :CurrentName DO
    BEGIN
        FullPath = :ParentPath || ' - ' || :CurrentName;
        NodeID = :CurNodeID;
        SUSPEND;
        -- 继续递归处理下一级子节点
        CALL GetChildPaths(:CurNodeID, :FullPath);
    END
END;

2. 调用存储过程 #

执行以下SQL获取结果：

SELECT FullPath FROM GetNodePaths;

执行后输出示例：

Parent
Parent - Child
Parent - Child - Child of child

若添加兄弟节点（如(4, 1, 'Apple')），会自动按字母排序输出：

Parent
Parent - Apple
Parent - Child
Parent - Child - Child of child

方法二：自定义递归函数+CONNECT BY #

1. 创建路径拼接函数 #

创建递归函数获取单个节点的完整路径：

CREATE FUNCTION GetFullPath(NodeID INTEGER) RETURNS VARCHAR(1000)
AS
DECLARE VARIABLE ParentID INTEGER;
DECLARE VARIABLE NodeName VARCHAR(100);
BEGIN
    -- 获取当前节点名称
    NodeName = (SELECT Name FROM Nodes WHERE NodeID = :NodeID);
    -- 判断是否为根节点
    IF (SELECT ParentNodeID FROM Nodes WHERE NodeID = :NodeID) = -1 THEN
        RETURN :NodeName;
    ELSE
        -- 递归获取父节点路径并拼接
        ParentID = (SELECT ParentNodeID FROM Nodes WHERE NodeID = :NodeID);
        RETURN GetFullPath(:ParentID) || ' - ' || :NodeName;
    END
END;

2. 使用CONNECT BY查询所有节点 #

通过CONNECT BY遍历层级结构，并用ORDER SIBLINGS BY保证兄弟节点按名称排序：

SELECT GetFullPath(NodeID) AS FullPath
FROM Nodes
START WITH ParentNodeID = -1
CONNECT BY PRIOR NodeID = ParentNodeID
ORDER SIBLINGS BY Name;

此方法会得到与存储过程完全一致的结果。

注意事项 #

• 路径长度：VARCHAR(1000)可根据实际节点名称长度调整，避免截断。
• 递归深度：Firebird默认递归深度限制为1024，若层级超过此限制，建议使用存储过程方式并优化递归逻辑。
• 性能：对于大量节点，建议给ParentNodeID和Name字段创建索引，提升查询和排序效率。

内容的提问来源于stack exchange，提问作者Steve