如何在给定节点下的SQL层次结构中选择所有叶节点? [英] How can I select all leaf nodes in a SQL hierarchy under a given node?

问题描述

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

I have a set of data that models a hierarchy of categories. A root category contains a set of top-level categories. Each top-level category contains a set of sub-categories.

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

Each sub category has a set of organizations. A given organization can appear in multiple sub categories.

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

The leaf nodes of this hierarchy are organizations. An organization can potentially appear in multiple sub-categories.

数据存储在三个SQL表中:

The data is stored in three SQL tables:

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

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

I would like to be able to select a list of all unique organizations under a given category or sub-category.

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

The way I'm doing it right now involves first figuring out which sub categories have been requested and then looping through each sub_category in code and performing a select to get all organizations mapped to that category. The results of each select are appended to an array. This array contains duplicates whenever an organization appears in multiple sub categories.

我很乐意用一个查询替换掉这个笨拙，该查询可以在给定层次结构中类别之一的ID的情况下有效地选择不同组织的列表.

I would love to replace this kludge with a query that can efficiently select a list of distinct organizations given an id of one of the categories in the hierarchy.

我正在使用PHP和MySQL开发此解决方案.

I am devloping this solution using PHP and MySQL.

感谢您的时间和建议.

推荐答案

假定您的层次结构始终正好是3个层次:

Assuming that your hierarchy is always exactly 3 levels deep:

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，则可以执行以下操作:

You can modify that by one level to allow you to pass a sub category id. If you don't know at the time whether or not you have a category id or a sub category id then you can do the following:

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在Smarties SQL中的树和层次结构，用于为层次结构建模的替代方法.无论如何，这样做可能是一个好主意.

If your hierarchy may have an unknown number of levels (or you think it might in the future) then check out Joe Celko's Trees and Hierarchies in SQL for Smarties for alternative ways to model a hierarchy. It's probably a good idea to do that anyway.

这篇关于如何在给定节点下的SQL层次结构中选择所有叶节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！