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

问题描述

以下是表中的一些条目：

 
 id r_id a_id p_id 
 
 
 
 1 9 9 0 
 2 9105108 
 3 9102 9 
 4 9106105 
 5 9108102 
 

是否可以使用SQL查询获得以下输出

 
 1 9 9 0 
 3 9 102 9 
 5 9108102 
 2 9105108 
 4 9106105 
 

想法是对行进行排序，以使p_id = x的行应位于a_id = x的行下方。

我希望这个问题有意义。

问候，

Mayank

编辑：

I我正在寻找PostgreSql

• 根项目的p_id = 0


• 没有缺少链接

解决方案

使用递归查询（PostgreSQL 8.4或更高版本）：

  / *测试数据：
 CREATE TABLE foo（id，r_id，a_id，p_id）AS 
 SELECT 1， 9,9,0 
 UNION ALL SELECT 2,9,105,108 
 UNION ALL SELECT 3,9,102,9 
 UNION ALL SELECT 4,9,106,105 
 UNION ALL SELECT 5,9,108,102 
; 
 * / 
 
-您需要的查询：
 WITH RECURSIVE sub（s_id，s_r_id，s_a_id，s_p_id，row）AS（
 SELECT id，r_id， a_id，p_id，从foo所在的1行作为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 
） 
 SELECT * FROM sub ORDER BY行； 
  

Following are some entries from a table:

id      r_id        a_id        p_id


1       9           9           0
2       9           105         108
3       9           102         9
4       9           106         105
5       9           108         102

Is it possible to get the following output using SQL query

1       9           9           0
3       9           102         9
5       9           108         102
2       9           105         108
4       9           106         105

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

I hope question makes sense.

Regards,
Mayank

EDIT:
I'm looking this for PostgreSql

• The root item has a p_id = 0
• There are no missing links

解决方案

Use a recursive query (PostgreSQL version 8.4 or later):

/* test data:
CREATE TABLE foo (id, r_id, a_id, p_id) AS
    SELECT  1,9,9,0
    UNION ALL SELECT 2,9,105,108
    UNION ALL SELECT 3,9,102,9
    UNION ALL SELECT 4,9,106,105
    UNION ALL SELECT 5,9,108,102        
;
*/

-- the query you need:
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
)
SELECT * FROM sub ORDER BY row;

这篇关于SQL查询：从表中获取有序行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！