SQL 查询:从表中获取有序行 - II

问题描述：

以下是表格中的一些条目:

Following are some entries from a table:

id r_id a_id p_id

1 9 9 02 9 105 1083 9 102 94 9 106 1055 9 108 1026 10 10 07 10 15 188 10 12 109 10 16 1510 10 18 12

1 9 9 0 2 9 105 108 3 9 102 9 4 9 106 105 5 9 108 102 6 10 10 0 7 10 15 18 8 10 12 10 9 10 16 15 10 10 18 12

我正在寻找一个 SQL 查询，它会给出如下输出:
1 9 9 03 9 102 95 9 108 1022 9 105 1084 9 106 1056 10 10 08 10 12 1010 10 18 127 10 15 189 10 16 15

I'm looking for an SQL query that will give an output like:

1 9 9 0 3 9 102 9 5 9 108 102 2 9 105 108 4 9 106 105 6 10 10 0 8 10 12 10 10 10 18 12 7 10 15 18 9 10 16 15

好吧，我在这里问了一个类似的问题，但是问题不完整，我也得到了一些很好的答案.编辑该问题可能会使答案不可接受，因此我没有在此处编辑并将其添加为新问题.

Well, I asked a similar question here but the question was not complete and I also got few excellent answers. Editing that question might make the answers unacceptable, so I did not edit and added this as a new question here.

根项目的 p_id = 0
对于一个 r_id 只能有一个 p_id = 0
显示的需要运行 Query 的表可能没有按照根排序.
我正在寻找可以在 PostgreSql 中工作的东西

我们的想法是以这样一种方式对行进行排序，即 {r_id, p_id} = x 的行应位于 {r_id, a_id} = x 的行下方.

The idea is to sort the rows in such a way that a row with {r_id, p_id} = x should come below the row with {r_id, a_id} = x.

答

修改上一个问题的答案，给出以下...

Modifying the answer to your previous question, gives the following...

WITH RECURSIVE sub(s_id, s_r_id, s_a_id, s_p_id, row) AS (
    SELECT id, r_id, a_id, p_id, 1 AS row FROM foo WHERE p_id = 0
UNION ALL
    SELECT id, r_id, a_id, p_id, (row + 1)  FROM foo JOIN sub ON s_a_id = p_id AND s_r_id = r_id
)
SELECT * FROM sub ORDER BY s_r_id, row;

SQL 查询:从表中获取有序行 - II

相关推荐