对 SQL 结果进行排序，其中引用同一表上另一条记录的每条记录出现在引用记录之后 [英] Order SQL results where each record referencing another record on the same table comes after the referenced record

问题描述

我有以下数据:

id, parent_id
1, 3
2, null
3, 2
4, 2

其中 parent_id 是对 SAME 表的引用.

Where parent_id is a reference to the SAME table.

我需要对这些列进行排序，以便每条记录都在其父记录之后(不一定紧随其后).

I need to sort these columns so that each record is after its parent (not necessarily immediately after).

所以我希望这个结果:

id, parent_id
2, null
3, 2
1, 3
4, 2

我猜想没有任何显着的架构更改就没有干净有效的方法来做到这一点，但如果有人能想到我在这里问的方法.

I'm guessing there is no clean efficient way to do this without any significant schema changes, but in case anybody can think of a method I'm asking this here.

一种可能的方法是进行多次查询，其中每次 parent_id 都必须在之前的查询结果之一中.但这不会很有效.

One possible method would be to do multiple queries where each time the parent_id must be in one of the previous queries results. But this wouldn't be very efficient.

推荐答案

你需要递归来完成这个:

You will need to recurse to accomplish this:

with recursive hier as (
  select *, 0 as hlevel
    from idparent
   where parent_id is null
  union all
  select c.*, p.hlevel + 1
    from hier p
    join idparent c on p.id = c.parent_id
)
select * from hier
 order by hlevel;

 id | parent_id | hlevel 
----+-----------+--------
  2 |           |      0
  3 |         2 |      1
  4 |         2 |      1
  1 |         3 |      2
(4 rows)

这篇关于对 SQL 结果进行排序，其中引用同一表上另一条记录的每条记录出现在引用记录之后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！