sqlite递归祖先查询

Question

我正在尝试弄清楚如何在分层表中使用递归查询。我需要获取给定记录的祖先，并且这些记录应该按照它们在层次结构中的级别排序。也就是说，第一条记录应该是顶级节点，下一条记录应该是一个子节点，然后是它的子节点，一直到被查询的记录。

考虑一个名为"food“的表，其中包含以下数据。这是一个简单的层次结构，除了顶部的记录之外，每个记录都有一个父记录。

id         | parent
-----------+---------
top        |
fruit      | top
red        | fruit
cherry     | red
apple      | red
orange     | fruit
clementine | orange
mandarin   | orange

为了理解关于这个主题的各种网页，我拼凑了下面的查询，它给出了" mandarin“记录的所有祖先，包括mandarin记录本身。

with recursive
    child_record(id) as (
        values('mandarin')

        union

        select parent
        from food, child_record
        where food.id = child_record.id
    )
select id from food
    where food.id in child_record;

但是，该查询返回记录的顺序在我看来是任意的：

fruit
mandarin
orange
top

我希望记录首先从最上面的记录开始排序，再从下到普通话记录。

top
fruit
orange
mandarin

如何构造查询以按所需顺序给出记录？

Answer 1

我想我明白了？我不太愿意说我知道，因为我仍然不太理解语法，但是下面的查询产生了我想要的结果。

with recursive
    child_record(level, id) as (
        values(0, 'mandarin')

        union

        select child_record.level+1, food.parent
        from food, child_record
        where food.id = child_record.id
    )
select child_record.level, food.id
from  food, child_record
where food.id = child_record.id
order by child_record.level desc;

Answer 2

我建议按rowid排序：

with recursive
    child_record(id) as (
        select 'mandarin'

        union

        select parent
        from food, child_record
        where food.id = child_record.id
    )
select id from food
    where food.id in child_record 
    order by food.rowid;

Answer 3

通过将this answer和您的组合在一起，我发现了两个可能的加速(我还没有测量到)：

1. 是常规连接而不是交叉连接( query).
2. union all的from food, child_record部分而不是union -结果已经过重复数据消除。

此外，我认为这个查询很自然地对其结果进行排序，但我重新添加了level字段以确保这一点。

with recursive ancestor(id, parent, level) as (
   select id, parent, 0
   from food
   where id = 'mandarin' -- this is the starting point you want in your recursion
   union all
   select f.id, f.parent, a.level + 1
   from food f
     join ancestor a on a.parent = f.id  -- this is the recursion
) 
select id
from ancestor
order by level;

┌──────────┐
│    id    │
├──────────┤
│ mandarin │
│ orange   │
│ fruit    │
│ top      │
└──────────┘