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_id1 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屋!