SQL中n:m继承关系的连接

Question

我想要加入一个表格，其中包含群组之间的n:m关系（群组在另一个表格中定义）。 这个表格只有列出成员群组ID和父群组ID的条目。

给定这个结构：

id(int) | member_group_id(int) | parent_group_id(int)

“基本”查询看起来像这样：

select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1 
join group_member_group as p2 
on p2.member_group_id = p1.member_group_id

“基础”查询正确显示了所有关系（我通过手动检查了）。问题出现在我尝试对这个查询应用where子句来过滤一个特定组作为“起点”（我想要其所有父组）时，它只返回最近的父组。例如像这样：

select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1 
join group_member_group as p2 
on p2.member_group_id = p1.member_group_id
where p1.group_id = 1

有人能给我提示如何修复这个问题吗？或者有不同的方法来实现这个问题。 （我想我总是可以在服务器端的C ++源代码中完成此操作，但我必须传输一个具有高增长潜力的完整表格到应用服务器。）

更新：

select p1.group_id, p2.group_id, p1.member_group_id, p2.member_group_id
from group_member_group as p1 
join group_member_group as p2 
on p2.group_id = p1.member_group_id

打字错误已确认。现在我无法通过继承期的第一级。感谢您指出这一点。

更新2：预期结果

id | group_id | member_group_id
--------------------------------
1  |    1     |    2
2  |    2     |    3
3  |    3     |    4
4  |    4     |    5
5  |    5     |    6
6  |    6     |    7

预期结果：

ids

2
3
4
5
6
7

- Nightmares

---

你的期望结果是什么？请以表格形式给出一些示例。 - Mosty Mostacho

请参见上面的更新2以获取预期结果。基本上，我想要一个给定表（在示例1中）直接或间接继承的所有表。 - Nightmares

Answer 1

on p2.member_group_id = p1.member_group_id

看起来像是一个错误。

也许你想输入：

on p2.parent_group_id = p1.member_group_id

也许你应该寻找“嵌套集模型”来处理父子关系。

在那个模型中，查询变得更加容易。

- denied

---

这不起作用，因为一个组可以属于N个其他组，这些组又可以属于N个组，依此类推... - Nightmares

如果您有多对多关系（据我所知）-那么您应该使用另一个表来存储这些关系。例如，表groups_relations（parent_id number，child_id number）。 - denied

我不能使用嵌套的父子关系是因为它们类似于树形模型。这意味着一个分支中的组不能成为另一个分支中组的成员。 - Nightmares

我上面描述的那个表格就是你刚才所描述的。包含组、名称等信息的表格是另一个独立的表格。上面的表格仅用于映射多对多关系（也称为n:m（n到m））。 - Nightmares

并不完全正确。实际上，您的表中每一行都存储了2个级别（父-子）。因此，对于深度N，您必须使用N-2个自连接。恐怕在MySQL中，如果不知道N的情况下，无法在一个查询中提取所有级别。在ORACLE中，您可以使用CONNECT BY子句，但是MySQL中没有这样的功能。 - denied

Answer 2

如果你愿意这么称呼它的话，我已经开发出了另一种解决方案，因为从技术上讲它不再是一个单一的查询。

我编写了一个MySQL存储过程，它完全符合我的要求。它搜索单个节点的所有父级，然后搜索下一级的父级，以此类推，并检查是否找到新结果（需要检测循环，以防万一，虽然应用程序逻辑应该避免这种情况，但为了保险起见，我还限制了最大距离）。

但这真的很麻烦，而且必须针对每个需要使用它的表进行修改。

我想我会坚持使用树模型，并将其称为“账户”，如果你愿意这么称呼它们，放在多个组中。这意味着一个账户与多个组存在1:n的关系，而一个组与父级存在n:1的关系（n个组有1个父级，该父级可能有另一个父级，直到“根”节点（经典树形结构））。

- Nightmares