SQL 查询：从表中获取有序行

发布于 2024-11-08 03:20:13 字数 562 浏览 0 评论 0原文

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

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

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

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

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

我希望问题有意义。

问候，
玛雅克

编辑：
我正在寻找 PostgreSql

根项目有一个 p_id = 0
没有丢失的链接

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

意中人 2024-11-15 03:20:13

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

/* 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;

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;

回复收藏 0 原文

紅太極 2024-11-15 03:20:13

以下内容改编自有效的 SQL Server 2005 解决方案。

我做了一些假设

根项有一个 p_id = 0
没有丢失的链接

SQL 语句

;WITH RECURSIVE q AS (
    SELECT  *
            , 1 AS Level
    FROM    ATable 
    WHERE   p_id = 0
    UNION ALL
    SELECT  t.*
            , Level = Level + 1
    FROM    q
            INNER JOIN ATable t ON t.p_id = q.a_id          
)
SELECT  *
FROM    q
ORDER BY
        Level

Following is adapted from a working SQL Server 2005 solution.

I have made some assumptions

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

SQL Statement

;WITH RECURSIVE q AS (
    SELECT  *
            , 1 AS Level
    FROM    ATable 
    WHERE   p_id = 0
    UNION ALL
    SELECT  t.*
            , Level = Level + 1
    FROM    q
            INNER JOIN ATable t ON t.p_id = q.a_id          
)
SELECT  *
FROM    q
ORDER BY
        Level

回复收藏 0 原文

~没有更多了~