MySQL查询如何进行上下级关系查询（mysql 上下级查询）

MySQL查询：如何进行上下级关系查询？

对于一个大型系统来说，通常会有一个复杂的层级结构，比如部门、员工、客户等，这些数据之间存在着上下级关系。在实际应用场景中，我们可能会遇到需要查询某个节点及其所有子节点信息的需求。那么在MySQL中，我们该如何进行上下级关系的查询呢？下面我们通过几个例子来学习一下。

例一：使用递归方式查询某个节点的所有子节点

假设我们有如下的表结构：

“`sql

CREATE TABLE `departments` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT ”,

`parent_id` int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

其中，`parent_id`表示当前节点的父节点ID，如果是根节点则为0。现在我们需要查询部门ID为1的节点及其所有子节点信息，可以使用递归方式实现：

```sql
WITH RECURSIVE temp_tree AS (
  SELECT id, name, parent_id FROM departments WHERE id = 1
  UNION ALL
  SELECT d.id, d.name, d.parent_id FROM departments AS d 
    JOIN temp_tree ON d.parent_id = temp_tree.id
)
SELECT * FROM temp_tree;

这里使用了`WITH RECURSIVE`语法来定义了一个临时表，然后通过递归查询所有子节点信息。在具体实现中，我们首先查询出部门ID为1的节点信息，然后在后面将其作为父节点ID继续递归查询子节点，直到没有子节点为止。

如果要查询某一个子节点的所有父节点信息，则可以使用类似的方式进行编写：

“`sql

WITH RECURSIVE temp_tree AS (

SELECT id, name, parent_id FROM departments WHERE id = 5

UNION ALL

SELECT d.id, d.name, d.parent_id FROM departments AS d

JOIN temp_tree ON d.id = temp_tree.parent_id

)

SELECT * FROM temp_tree;

例二：使用闭包表方式查询某个节点的所有子节点

闭包表是一种比较高效的查询上下级关系的方式。在闭包表中，每一行记录表示一个节点之间的上下级关系，只需要建立一张专门的表来维护这种关系即可。我们可以借助一个`closure_table`的表来建立这种关系：

```sql
CREATE TABLE `closure_table` (
  `ancestor` int(11) NOT NULL,
  `descendant` int(11) NOT NULL,
  `depth` int(11) NOT NULL,
  PRIMARY KEY (`ancestor`, `descendant`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这个表中，`ancestor`表示上级节点ID，`descendant`表示下级节点ID，`depth`表示两个节点之间的距离，即层级深度。假设我们已经有了部门信息表，我们可以通过以下方式来生成闭包表：

“`sql

INSERT INTO closure_table

SELECT

ancestor.id AS ancestor,

descendant.id AS descendant,

(COUNT(*) – 1) AS depth

FROM

departments AS descendant

JOIN departments_ancestor AS descendant_ancestor

ON descendant_ancestor.descendant_id = descendant.id

JOIN departments AS ancestor

ON ancestor.id = descendant_ancestor.ancestor_id

GROUP BY ancestor.id, descendant.id;

这里我们需要先关联`departments_ancestor`表，该表中每一行记录表示两个部门之间的上下级关系，然后通过`GROUP BY`语句来计算出每个部门之间的层级深度。假设我们想要查询部门ID为1的节点及其所有子节点信息，可以使用以下查询语句：

```sql
SELECT d.* FROM departments AS d 
  JOIN closure_table AS ct ON d.id = ct.descendant
WHERE ct.ancestor = 1;

该查询语句会首先在`closure_table`表中通过`ancestor`字段找到所有子节点，然后再通过`JOIN`语句和`departments`表关联，查询出所有子节点信息。

需要注意的是，闭包表的建立和维护相对比较复杂，对于大型系统来说，如果层级结构比较稳定，且查询频繁，建议使用闭包表方式来维护上下级关系。如果层级结构比较简单，且查询需求不是很频繁，可以考虑使用递归方式实现。

总结

以上是MySQL中进行上下级关系查询的两种方式，读者可以根据自己的需求选择合适的方式来实现。递归方式相对比较简单，但在大数据量的情况下可能存在性能问题；闭包表方式可以实现高效的查询，但需要额外维护闭包表，代码实现也比较复杂。在实际应用中，需要根据具体情况进行选择。