postgresql 如何递归查询一个由相互链接的项目组成的表？

8yparm6h 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(146)

我有一个存储项目的系统，并且可以接收替换现有项目的新项目。每个项目都有其自己的唯一ID，当接收到替换前一个项目的项目时，我们会引用它所替换的项目。
这将导致

从未收到更新的项目（referenced_item_id中不存在其ID）
已更新一次或多次的项目（这会建立更新清单）

个项目

id Message
-- -------
1  Original item
2  This replaces item #1
3  Standalone item
4  This replaces item #2

项目引用

item_id referenced_item_id
------- ------------------
2       1
4       2

如果我的ID是1，我如何递归查询表以获得4项？
另一个有用的查询是，给定一个ID，我如何才能获得所有相关项？
注意：我愿意改进数据的存储方式，但是由于它遵循现有的规范，所以以这种方式存储数据。理想情况下，为了简单起见，我希望保留这种格式。

postgresql

来源：https://stackoverflow.com/questions/74383898/how-to-recursively-query-a-table-of-items-that-are-linked-to-each-other

1条答案

按热度按时间

1sbrub3j1#

多亏了这个视频https://youtu.be/QNfnuK-1YYY?t=35，我才能构建一个解决方案。
下面的查询将返回一个项目的最新版本（给定一个ID）。

WITH RECURSIVE item_updates AS (
  select
    id,
    message
  FROM
    item 
  WHERE
    id = 1
  UNION
    select
      i.id,
      i.message
    FROM
      item_updates prev_item 
    join item_reference on item_reference.referenced_item_id = prev_item.id
    join item i on i.id = item_reference.item_id
) 
select 
  * 
from 
  item_updates
order by 
  id desc
limit 
  1
;

如果您从最终选择中移除限制，则可以获得所有指定id的更新版本-

WITH RECURSIVE item_updates AS (
    select
      id,
      message
    FROM
      item 
    WHERE
      id = 1
    UNION
      select
        i.id,
        i.message
      FROM
        item_updates prev_item 
      join item_reference on item_reference.referenced_item_id = prev_item.id
      join item i on i.id = item_reference.item_id
  ) 
 select * from item_updates;

我不确定这是否是最有表现力的方法，但它很有效。

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 如何递归查询一个由相互链接的项目组成的表？

个项目

项目引用

1条答案

相关问题

热门标签

最新问答