具有多个级别的多重关系父/子 [英] Multiple relations parent/child with multiple levels

问题描述

我有一个名为 companys 的 MySQL 表，如下所示:

I have a MySQL table named companies like this:

+---------+-----------+-----------+
| id_comp | comp_name | id_parent |
+---------+-----------+-----------+
|       1 | comp1     |      NULL |
|       2 | comp2     |         1 |
|       3 | comp3     |         2 |
|       4 | comp4     |         2 |
|       5 | comp5     |         2 |
|       6 | comp6     |         1 |
|       3 | comp3     |         6 |
|       5 | comp5     |         6 |
|       7 | comp7     |         6 |
|       4 | comp4     |         6 |
|       8 | comp8     |         4 |
+---------+-----------+-----------+

每个公司可能有多个父级(例如:comp3，它是comp2 和comp6 的子级)，每个父级可能有多个子级每个孩子都可以是多个孩子的父母，等等......所以，它可以有无限的级别(关系).

Each company may have multiple parents (ex: comp3, which is child of comp2 and comp6), each parent may have multiple childs and each child can be a parent itself of multiple childs and so on... So, it can have unlimited levels (relations).

我研究了几种解决方案(http://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child、http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)，但我不这么认为适合我的问题，因为同一家公司(基于 id_comp 列)可以有多个父公司.

I researched several solutions (http://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), but I don't think it fits my the problem since the same company (based on id_comp column) can have multiple parents.

关于这个我有两个问题:

I have two questions regarding this:

1. 如果我有数千个关系(可扩展)，这是正确的方法吗?
2. 如果给定一个 name(它是唯一的，基于 id_comp)查询，我该如何选择它的兄弟(相同的 parent_id)、它的直接父母，及其直系子节点.

1. Is this the right approach if I have thousands of relations (scalable) ?
2. How do I, given a name (which is unique, based on id_comp) query to select its brothers (same parent_id), its direct parent(s), and its direct child(s).

推荐答案

如果您需要处理分层数据(获取所有祖先/后代可能很棘手)，Mysql 不是最佳选择.但是，如果您只关心找到直接的父母/子女，那么您的表格应该没问题(尽管我可能会将其分解为单独的 Company 和 CompanyParent 表格，以便不会多次输入公司名称).

Mysql isn't the best choice if you need to work with hierarchical data (getting all ancestors/descendants can be tricky). But if all you care about is finding direct parents/children, your table should be fine (although I might break it out into separate Company and CompanyParent tables so that the company name isn't entered multiple times).

这会给你兄弟:

select name
from companies 
where id_parent in (select id_parent from companies where id_comp = @company_id)
and id_comp <> @company_id
group by name;

这会给你直接的父母:

select p.name
from companies p
join companies c on p.id = c.id_parent
where c.id_comp = @company_id
group by c.name;

这会给你直接的孩子:

select c.name
from companies p
join companies c on p.id = c.id_parent
where p.id_comp = @company_id
group by c.name;

这篇关于具有多个级别的多重关系父/子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！