时间戳

首页 > 解决方案 > 树的内部连接根到表

database - 树的内部连接根到表

问题描述

所以，我基本上有两张桌子。

|   id   | marketGroupID |
|--------|---------------|
|   1    |      134      |
|   2    |      868      |
|   3    |      387      |

第二张表：

| marketGroupid | parentGroupID |
|---------------|---------------|
|   134         |      5987     |
|   5987        |      NULL     |
|   868         |      8796     |
|   8796        |      387      |
|   387         |      NULL     |

最终结果应如下所示：

|   id   | marketGroupID | parentGroupID |
|--------|---------------|---------------|
|   1    |      134      |     5987      |
|   2    |      868      |     387       |
|   3    |      387      |     387       |

现在我知道如何通过 INNER JOIN 两个表

SELECT table1.id,table1.marketGroupID,table2.parentGroupID FROM 'table1' INNER JOIN 'table2' ON table1.marketGroupID=table2.marketGroupID;

现在这里的问题是这只会显示直接的父母。但我想要根父母。我不知道根父节点和子节点之间会有多少个节点。我所知道的是，一旦 parentGroupID 为 NULL，就会到达根父级。根父节点可能已经是 marketGroupID，就像 id=3 一样，或者在 id=2 之间可能有 3 个节点。

这里的重点是我不想要中间步骤。“重复”中的第一个答案正是这样做的。此外，它是否假设了最大数量的中间步骤。每一步都有另一个左连接。我也不知道根父级最终会在哪个级别，因为我不知道我有多少中间步骤。所以我必须从左到右遍历每一行，直到遇到 null 和之前的最后一个值。

使用 cte 假设的答案只有一张表。但我也有桌子。而且我仍然只想要根父级，中间什么都没有。

从技术上讲，我什至不需要原始市场组 ID。对于表一中的每个 id，我只需要根 parentgroupid 就可以了。

标签： databasesqlitetreeinner-join

解决方案

这是使用递归 CTE的经典案例。

从一个为第一个表中的每个 id 生成一组行的表开始，该表遍历所有父链：

WITH RECURSIVE parents(id, child, parent) AS
  (SELECT id, NULL, marketGroupID FROM table1
  UNION ALL
   SELECT p.id, p.parent, t2.parentGroupID
   FROM table2 AS t2
   JOIN parents AS p ON p.parent = t2.marketGroupID)
SELECT * FROM parents;
id          child       parent
----------  ----------  ----------
1                       134
2                       868
3                       387
1           134         5987
2           868         8796
3           387
1           5987
2           8796        387
2           387

在这些结果中，子列为空的行是基组 id，两列中都有值的行是指向根的链接链中的中间值，父列为空的行具有每个的最终根 groupid行table1。这是我们感兴趣的最后一组行。添加跟踪原始子组并过滤掉非根行会给出最终结果：

WITH RECURSIVE parents(id, market, child, parent) AS
  (SELECT id, marketGroupID, NULL, marketGroupID FROM table1
  UNION ALL
   SELECT p.id, p.market, p.parent, t2.parentGroupID
   FROM table2 AS t2
   JOIN parents AS p ON p.parent = t2.marketGroupID)
SELECT id, market AS marketGroupID, child AS parentGroupID
FROM parents
WHERE parent IS NULL
ORDER BY id;
id          marketGroupId  parentGroupID
----------  -------------  -------------
1           134            5987
2           868            387
3           387            387

从技术上讲，我什至不需要原始市场组 ID。对于表一中的每个 id，我只需要根 parentgroupid 就可以了。

WITH RECURSIVE parents(id, child, parent) AS
  (SELECT id, NULL, marketGroupID FROM table1
  UNION ALL
   SELECT p.id, p.parent, t2.parentGroupID
   FROM table2 AS t2
   JOIN parents AS p ON p.parent = t2.marketGroupID)
SELECT id, child AS parentGroupID FROM parents WHERE parent IS NULL ORDER BY ID;

推荐阅读