在 Postgres 中实现一个类似于链表的排序列

Question

>假设我有一个数据库表teams有一个排序列position，如果它是最后一个结果，则可以null位置，或者下一个team的ID定位比该团队高一个。这将导致一个始终严格排序的列表(如果您使用 ints，则必须在插入新team时管理所有其他位置值，即将它们全部递增 1(，并且插入变得不那么复杂......

但是事实证明，要检索此表作为排序查询很棘手，这就是我目前所处的位置：

WITH RECURSIVE teams AS (  
SELECT *, 1 as depth FROM team
UNION
SELECT t.*, ts.depth + 1 as depth
FROM team t INNER JOIN teams ts ON ts.order = t.id 
SELECT
id, order, depth
FROM
teams
;

这让我得到这样的东西：

id | order | depth
----+-------+-------
53 |    55 |     1
55 |    52 |     1
55 |    52 |     2 
52 |    54 |     2
52 |    54 |     3
54 |       |     3
54 |       |     4

哪种反映了我需要在排序方面到达的位置(最大深度代表我想要的排序......(，但是我无法弄清楚如何更改查询以获得类似以下内容：

id | order | depth
----+-------+-------
53 |    55 |     1
55 |    52 |     2 
52 |    54 |     3
54 |       |     4

但是，似乎我更改了它抱怨我关于在id和depth上应用GROUP BY的查询......我如何从我现在的位置到达我想去的地方？

Answer 1

您的递归查询应该从某个地方开始(现在您在第一个子查询中选择整个表(。我建议从列为 null 的最后一条记录开始order然后走到第一条记录：

with recursive team(id, ord) as (values(53,55),(55,52),(52,54),(54,null)),
teams as (
select *, 1 as depth from team where ord is null -- select the last record here
union all
select t.*, ts.depth + 1 as depth
from team t join teams ts on ts.id = t.ord) -- note that the JOIN condition reversed comparing to the original query
select * from teams order by depth desc; -- finally reverse the order

┌────┬────

──┬───────┐ │ id │ ord │ 深度 │ ├────┼──────┼───────┤ │ 53 │ 55 │ 4 │ │ 55 │ 52 │ 3 │ │ 52 │ 54 │ 2 │ │ 54 │ ░░░░ │ 1 │ └────┴──────┴──