如何在SQLite递归查询中按深度与兄弟节点顺序排序树形结构？

要实现按深度（垂直）和sibling字段指定的兄弟顺序（水平）排序，我们需要调整原递归查询的逻辑——不再直接按boss关联子节点，而是利用sibling的链表关系来遍历兄弟节点，同时维护排序路径保证输出顺序正确。

修改后的递归查询语句 #

WITH RECURSIVE tree(name, level, sort_path) AS (
  -- 初始根节点：找到无上级的Alice，层级0，排序路径初始化为节点名
  SELECT 
    name, 
    0, 
    name
  FROM org 
  WHERE boss IS NULL
  
  UNION ALL
  
  -- 第一部分：递归当前节点的「第一个子节点」
  -- 第一个子节点的判定：父节点是当前节点，且没有其他节点的sibling指向它
  SELECT 
    child.name, 
    parent.level + 1, 
    parent.sort_path || '/' || child.name
  FROM tree parent
  JOIN org child ON child.boss = parent.name
  WHERE NOT EXISTS (
    SELECT 1 FROM org sibling_check WHERE sibling_check.sibling = child.name
  )
  
  UNION ALL
  
  -- 第二部分：递归当前节点的「下一个兄弟节点」
  -- 利用sibling字段找到下一个兄弟，同时修正排序路径为兄弟节点的路径
  SELECT 
    sibling.name, 
    current.level, 
    replace(current.sort_path, current.name, sibling.name)
  FROM tree current
  JOIN org sibling ON sibling.name = current.sibling
)
-- 按排序路径输出，同时添加缩进
SELECT substr('..........', 1, level*3) || name 
FROM tree
ORDER BY sort_path;

逻辑解释 #

1. 根节点初始化：先定位树形结构的根节点（boss IS NULL的Alice），初始化层级和排序路径。
2. 递归子节点：每次找到当前节点的第一个子节点（即没有被其他兄弟节点的sibling指向的节点），层级+1，同时将子节点名称追加到排序路径后。
3. 递归兄弟节点：处理完当前节点的所有子节点后，通过sibling字段找到下一个兄弟节点，层级保持不变，修正排序路径为兄弟节点的路径。
4. 最终排序：通过sort_path字段保证输出是深度优先且兄弟节点按指定顺序排列的树形结构。

测试结果 #

执行上述查询后，会得到你期望的输出：

Alice
...Cindy
......Gail
......Fred
...Bob
......Dave
......Emma

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