PostgreSQL在一行中查看同一张表的一组相关记录

Gela 发表于 Dev

格拉

让我们认为下表包含链接记录：

CREATE TABLE tbl(id integer,
                 name text,
                 next_id integer,
                 UNIQUE(id),
                 FOREIGN KEY(next_id) REFERENCES tbl(id));

将给定的数据插入其中：

INSERT INTO tbl(id,  name, next_id)
         VALUES( 1, 'x_0',       2),
               ( 2, 'x_1',       3),
               ( 3, 'x_2',    null),
               ( 4, 'y_0',       5),
               ( 5, 'y_1',       6),
               ( 6, 'y_2',       7),
               ( 7, 'y_3',    null);

我们可以自我联接该表，以获得在单行中查看的每组链接数据：

SELECT
   *
FROM
   tbl t_1 LEFT JOIN tbl t_2 ON t_1.next_id = t_2.id
           LEFT JOIN tbl t_3 ON t_2.next_id = t_3.id
           LEFT JOIN tbl t_4 ON t_3.next_id = t_4.id
WHERE t_1.id = 1 OR t_1.id = 4
ORDER BY t_1.id ASC;

现在的问题是：当链接集中的元素数和集合数是任意的时，在一般情况下如何编写一个可以实现相同目标的查询。提前谢谢了

克林

您不能编写产生未知列数的查询。查询结果的结构由该查询定义。但是，您可以将结果汇总到数组中。这可以通过如下递归查询来完成：

with recursive cte as (
    select
        id,
        next_id,
        array[id] as ids,
        array[name] as names
    from tbl t
    where not exists (
        select from tbl s
        where s.next_id = t.id)
union all
    select 
        c.id,
        t.next_id,
        ids || t.id,
        names || t.name
    from tbl t
    join cte c on c.next_id = t.id
)
select distinct on (id)
    id,
    ids,
    names
from cte
order by id, cardinality(ids) desc

 id |    ids    |       names
----+-----------+-------------------
  1 | {1,2,3}   | {x_0,x_1,x_2}
  4 | {4,5,6,7} | {y_0,y_1,y_2,y_3}
(2 rows)

Db <>提琴。

在文档中阅读有关递归查询的信息。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。