SQL 查询:从表中获取有序行 - II [英] SQL Query: Fetch ordered rows from a table - II

问题描述

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

Following are some entries from a table:

id r_id a_id p_id

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！