MySQL上下级查询如何查询父级和子级数据（mysql 上下级查询）

MySQL上下级查询：如何查询父级和子级数据？

在MySQL中，如果需要查询某个节点的所有子节点以及其祖先节点（父节点、祖父节点等），我们可以采用一种常见的技巧——递归查询。下面，我们将详细介绍如何在MySQL中实现递归查询，以及一些实例。

一、建立表格

首先我们需要建立一个表格，此处以department表为例，包含下列字段：

“`sql

CREATE TABLE department (

id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘自增ID’,

name VARCHAR(40) NOT NULL COMMENT ‘名称’,

parent_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘父级ID’,

PRIMARY KEY (id)

);

其中，parent_id表示该节点的父节点ID，顶级节点的parent_id为0。

二、插入数据

接下来我们插入一些测试数据：

```sql
INSERT INTO department (id, name, parent_id)
VALUES (1, '总部', 0),
       (2, '财务部', 1),
       (3, '人事部', 1),
       (4, '研发部', 1),
       (5, '财务一部', 2),
       (6, '财务二部', 2),
       (7, '人事一部', 3),
       (8, '人事二部', 3),
       (9, '开发一部', 4),
       (10, '开发二部', 4),
       (11, '测试一部', 9),
       (12, '测试二部', 9),
       (13, '设计部', 4),
       (14, '前端部', 13);

三、递归查询

要实现递归查询，我们可以使用MySQL的WITH RECURSIVE子句。这个子句可以让我们定义一个递归查询，同时也可以定义递归查询的退出条件。接下来，我们将为大家演示如何使用WITH RECURSIVE子句查询所有父级和子级部门。

查询某个节点的所有子节点

“`sql

WITH RECURSIVE cte AS (

SELECT id, name, parent_id FROM department WHERE id = 4 — 这里需要替换为你自己的节点ID

UNION ALL

SELECT department.id, department.name, department.parent_id FROM department

INNER JOIN cte ON department.parent_id = cte.id

)

SELECT * FROM cte;

在这个例子中，我们查询了节点ID为4的所有子节点。我们在WITH RECURSIVE子句中指定了查询的起始节点（SELECT id, name, parent_id FROM department WHERE id = 4）。然后，我们使用UNION ALL关键字定义了递归部分，即根据上一级节点查询下一级节点（SELECT department.id, department.name, department.parent_id FROM department INNER JOIN cte ON department.parent_id = cte.id），直到所有子节点查询完毕。

查询某个节点的祖先节点

现在我们来看看如何查询某个节点的祖先节点，其实也是同样的套路：

```sql
WITH RECURSIVE cte AS (
SELECT id, name, parent_id FROM department WHERE id = 14 -- 这里需要替换为你自己的节点ID
UNION ALL
SELECT department.id, department.name, department.parent_id FROM department 
INNER JOIN cte ON department.id = cte.parent_id
)
SELECT * FROM cte;

此时我们查询了节点ID为14的所有祖先节点。WITH RECURSIVE子句中的第一个查询仍然是指定起始节点，但是递归查询的部分稍有不同，我们根据上一级节点的parent_id查询下一级节点。同样的，直到所有祖先节点查询完毕。

四、完整代码

为了方便大家参考和使用，我们把完整的递归查询代码代码分享给大家：

“`sql

— 建立表格

CREATE TABLE department (

id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘自增ID’,

name VARCHAR(40) NOT NULL COMMENT ‘名称’,

parent_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘父级ID’,

PRIMARY KEY (id)

);

— 插入数据

INSERT INTO department (id, name, parent_id)

VALUES (1, ‘总部’, 0),

(2, ‘财务部’, 1),

(3, ‘人事部’, 1),

(4, ‘研发部’, 1),

(5, ‘财务一部’, 2),

(6, ‘财务二部’, 2),

(7, ‘人事一部’, 3),

(8, ‘人事二部’, 3),

(9, ‘开发一部’, 4),

(10, ‘开发二部’, 4),

(11, ‘测试一部’, 9),

(12, ‘测试二部’, 9),

(13, ‘设计部’, 4),

(14, ‘前端部’, 13);

— 查询某个节点的所有子节点

WITH RECURSIVE cte AS (

SELECT id, name, parent_id FROM department WHERE id = 4 — 这里需要替换为你自己的节点ID

UNION ALL

SELECT department.id, department.name, department.parent_id FROM department

INNER JOIN cte ON department.parent_id = cte.id

)

SELECT * FROM cte;

— 查询某个节点的祖先节点

WITH RECURSIVE cte AS (

SELECT id, name, parent_id FROM department WHERE id = 14 — 这里需要替换为你自己的节点ID

UNION ALL

SELECT department.id, department.name, department.parent_id FROM department

INNER JOIN cte ON department.id = cte.parent_id

)

SELECT * FROM cte;

通过以上介绍，我们相信您已经掌握了如何在MySQL中使用递归查询来查询父级和子级数据的技巧。