如何选择 SQL 层次结构中给定节点下的所有叶节点？

Question

我有一组对类别层次结构进行建模的数据。 根类别包含一组顶级类别。 每个顶级类别都包含一组子类别。

每个子类别都有一组组织。 给定组织可以出现在多个子类别中。

该层次结构的叶节点是组织。 一个组织可能会出现在多个子类别中。

数据存储在三个 SQL 表中：

organizations
organization_id organization_name
1               Org A
2               Org B
3               Org C
4               Org D
5               Org E
6               Org F

categories
category_id parent_id category_name
0           NULL      Top Level Category
1           0         First Category
2           0         Second Category
3           1         Sub Category A
4           1         Sub Category B
5           1         Sub Category C
6           2         Sub Category D

organizations_categories -- Maps organizations to sub_categories
organization_id category_id
1               3
2               3
2               6
3               4
4               4
5               4
6               5
6               4
7               6
8               6

我希望能够选择给定类别或子类别下的所有唯一组织的列表。

我现在执行此操作的方式包括首先确定已请求哪些子类别，然后循环遍历代码中的每个 sub_category 并执行选择以将所有组织映射到该类别。 每个选择的结果都附加到一个数组中。 每当一个组织出现在多个子类别中时，该数组就会包含重复项。

我很乐意用一个查询来替换这个拼凑，该查询可以在给定层次结构中某个类别的 id 的情况下有效地选择不同组织的列表。

我正在使用 PHP 和 MySQL 开发这个解决方案。

感谢您的时间和建议。

Answer 1

假设您的层次结构始终为 3 级深：

SELECT DISTINCT
     O.organization_id,
     O.organization_name
FROM
     Categories CAT
INNER JOIN Categories SUB ON
     SUB.parent_id = CAT.category_id
INNER JOIN Category_Organizations CO ON
     CO.category_id = SUB.category_id
INNER JOIN Organizations O ON
     O.organization_id = CO.organization_id
WHERE
     CAT.category_id = @category_id

您可以将其修改一级以允许传递子类别 ID。 如果您当时不知道您是否有类别 id 或子类别 id，那么您可以执行以下操作：

SELECT DISTINCT
     O.organization_id,
     O.organization_name
FROM
     Categories CAT
LEFT OUTER JOIN Categories SUB ON
     SUB.parent_id = CAT.category_id
INNER JOIN Category_Organizations CO ON
     CO.category_id IN (CAT.category_id, SUB.category_id)
INNER JOIN Organizations O ON
     O.organization_id = CO.organization_id
WHERE
     CAT.category_id = @category_id

如果您的层次结构可能具有未知数量的级别（或者您认为将来可能），那么 您可以执行以下操作：请查看 Joe Celko 为聪明人编写的 SQL 中的树和层次结构，了解建模的其他方法一个层次结构。 无论如何，这样做可能是个好主意。

Answer 2

不确定您的数据模型是否允许这样做，但您可以使用单个索引列和二叉树轻松将此信息存储在单个“OrganizationTree”表中。 还有一个好处是，您可以使用单个查询，无需修改即可在类别、子类别或组织级别进行搜索（例如，给我 X 子类别的所有结果）

希望这会有所帮助。

亚当。