假定您的层次结构始终正好是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.