如何使用MySQL查询上下级机构

在数据库管理中，查询上下级机构关系是一个常见的需求。MySQL作为一款广泛使用的关系型数据库，提供了多种方法来实现这一功能。下面将详细介绍如何使用MySQL查询上下级机构。

数据结构设计

我们需要设计合理的数据结构来存储机构关系。通常会有一张机构表，表中至少包含机构ID、机构名称以及父机构ID等字段。例如，表名为 organization，字段有 org_id（机构ID，主键）、org_name（机构名称）、parent_id（父机构ID）。合理的数据结构是准确查询的基础。

递归CTE查询

递归CTE（Common Table Expression）是MySQL 8.0及以上版本支持的一种强大的查询方式。通过递归CTE，可以轻松地查询出一个机构的所有下级机构。示例代码如下：

WITH RECURSIVE OrgHierarchy AS (
    SELECT org_id, org_name, parent_id
    FROM organization
    WHERE org_id = [起始机构ID]
    UNION ALL
    SELECT o.org_id, o.org_name, o.parent_id
    FROM organization o
    INNER JOIN OrgHierarchy oh ON o.parent_id = oh.org_id
)
SELECT * FROM OrgHierarchy;

在这段代码中，首先定义了一个递归CTE OrgHierarchy，起始部分查询出指定起始机构的信息，然后通过递归部分不断地将下级机构加入结果集。

自连接查询

对于不支持递归CTE的MySQL版本，可以使用自连接查询来获取上下级机构关系。例如，要查询某个机构的所有下级机构，可以使用如下查询：

SELECT sub.org_id, sub.org_name
FROM organization main
JOIN organization sub ON main.org_id = sub.parent_id
WHERE main.org_id = [起始机构ID];

这种方法通过将表与自身进行连接，根据父机构ID找到下级机构。不过，这种方法在处理多层级关系时可能会比较复杂，需要多次连接。

查询上级机构

查询上级机构与查询下级机构类似。使用递归CTE查询上级机构时，代码如下：

WITH RECURSIVE ParentHierarchy AS (
    SELECT org_id, org_name, parent_id
    FROM organization
    WHERE org_id = [起始机构ID]
    UNION ALL
    SELECT o.org_id, o.org_name, o.parent_id
    FROM organization o
    INNER JOIN ParentHierarchy ph ON o.org_id = ph.parent_id
)
SELECT * FROM ParentHierarchy;

这里的递归逻辑与查询下级机构相反，从起始机构开始向上追溯父机构。

通过合理设计数据结构，并运用递归CTE、自连接等查询方式，我们可以灵活地在MySQL中查询上下级机构关系，满足各种业务场景的需求。