PostgreSQL 15+ 如何基于next_id指针实现表格链式排序？

针对你需要按next_id链式指针排序记录的需求，无需使用循环或临时表，PostgreSQL的**递归CTE（Common Table Expression）**是最优解决方案，能直接实现链式排序逻辑。

实现代码 #

假设你的表名为your_table，执行以下SQL即可得到预期排序结果：

WITH RECURSIVE chain_sort AS (
    -- 锚点：定位所有链的起始节点（未被任何next_id指向的记录）
    SELECT 
        id, 
        name, 
        next_id,
        ARRAY[id] AS path
    FROM your_table
    WHERE id NOT IN (SELECT next_id FROM your_table WHERE next_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归：沿next_id关联后续节点，记录链式路径
    SELECT 
        t.id, 
        t.name, 
        t.next_id,
        cs.path || t.id AS path
    FROM your_table t
    JOIN chain_sort cs ON t.id = cs.next_id
)
SELECT id, name, next_id
FROM chain_sort
ORDER BY path;

逻辑说明 #

1. 锚点查询：筛选出所有未被其他记录的next_id指向的id，这些就是每条独立链的起点（比如示例中的1和7）。
2. 递归查询：通过JOIN将当前链的末尾记录与next_id指向的下一条记录关联，同时用数组path记录整条链的顺序。
3. 最终排序：按path数组排序，数组的元素顺序就是记录的先后顺序，完美匹配链式指针的要求。

性能优化建议 #

如果表数据量较大，为id和next_id创建索引可以显著提升递归查询的效率：

CREATE INDEX idx_your_table_id ON your_table(id);
CREATE INDEX idx_your_table_next_id ON your_table(next_id);

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