SQL 选择随机排序，但某些行必须按 'next' id 列分组

Question

我有一个有两列的表，id和next。两者都是文件的SHA256,id是主键，next是可空的，引用另一行的id。

我要做的是从随机排序的表中选择行，但同时:如果一行包含next中的值，则下一行的id/pk必须是next的值，从上一行。它本质上是一个随机查询，但保留某些行，这些行在一个序列中相互依赖。

随机部分很容易，就像SELECT * FROM table ORDER BY rand()一样，但我没有发现任何关于基于前一行值的排序。另一种选择是在查询之后在客户端手动排序，但是根据表的大小，这可能太昂贵了。

的例子:

<表类> id 下 tbody><<tr>空bccdd空e空f空g td>he我空

这是一个图行走问题。我假设你的结构是一组链表:

• 没有周期。
• next是唯一的

这些假设是基于您的命名。

有了这个假设，您可以使用一个相对简单的递归CTE来构造到每个id的路径，然后按该路径排序:

with recursive cte as (
select id, next, cast(id as text)  as path
from t
where not exists (select 1 from t t2 where t2.next = t.id)
union all
select t.id, t.next, (cte.path || coalesce('->' || t.id, ''))
from cte join
t
on cte.next = t.id
)
select id, next
from cte
order by path;

这是一个db<>小提琴