如何在PostgreSQL中使用递归查询连接字段值？

Question

我在PostgreSQL数据库中有一张表，它以树形式包含地址的部分，看起来像这样：

Id | Name         | ParentId
1  | London       | 0
2  | Hallam Street| 1
3  | Bld 26       | 2
4  | Office 5     | 3

我希望查询返回一个地址，该地址由所有祖先名称拼接而成。我需要结果表格长这样：

Id | Address
1  | London
2  | London, Hallam Street
3  | London, Hallam Street, Bld 26
4  | London, Hallam Street, Bld 26, Office 5

我想我需要使用WITH RECURSIVE查询，但是我找到的所有示例都使用了where子句，因此我必须加上WHERE name='Office 5'仅获取该特定行的结果。但我需要为我的初始表中的每一行提供一个连接的地址。如何实现这一点？

- mofoyoda

Answer 1

使用递归查询的技巧在于需要指定种子查询。这个查询决定了您的根节点，或者您构建的树的下降或上升的起点。 WHERE 子句的原因是为了建立种子 ID=1 或 Name=Bld 26。如果您希望每个记录都上升或下降（取决于您在联合选择中指定的内容），那么您应该放弃 WHERE 语句，以便所有记录都成为种子。

尽管如此，在您提供的示例中...您可能需要从种子开始使用 WHERE ID=1，写出子ID和父ID。然后在 Union'd SELECT 中，将导出的递归表与您正在选择的表连接，并将 Derived Recursive 表的 Child 连接到您的表的 parent 上。

像这样：

WITH RECURSIVE my_tree AS (
  -- Seed

  SELECT 
      ID as Child, 
      ParentID as Parent, 
      Name,
      Name as Address
  FROM <table>
  WHERE <table>.ID = 1


  UNION

  -- Recursive Term
  SELECT 
    table.id as Child, 
    table.parent_id as Parent, 
    table.name,
    t.address || ', ' || table.name as Address

  FROM my_tree as t 
    INNER JOIN <table> table ON
        t.Child = table.Parent_Id  
)
SELECT Child, Address from my_tree;

我以前没有使用过PostgreSQL，所以你可能需要花费一些时间来适应语法，但我认为这对于该关系型数据库来说相当准确。

- JNevill

---

“seed” 查询周围的括号是无用的（在我个人看来也很令人困惑）。此外，在 SQL 中，字符串字面量需要用单引号 (',') 包含，而不是双引号（双引号用于标识符）。 - user330315

谢谢@a_horse_with_no_name！我觉得括号里的种子很奇怪。我是从网上找到的postgresql示例中进行工作，以为它很重要。我使用Teradata，它看起来语法几乎相同，只是在WITH RECURSIVE子句中如果每个字段没有明确声明，它会抛出一个错误。 - JNevill

似乎有一个普遍的误解，即联合（或联合全部）查询需要括号，所以我不奇怪你在网上找到了这个。但我不知道这是从哪里来的。 - user330315

说实话，这个例子中种子被包裹在双括号中。虽然我从未使用过PostgreSQL，但我拒绝相信它要求在任何地方都要加上双括号。我听说了太多好的事情。 - JNevill