MySQL 同一表多对多关系

在MySQL中，同一表上的多对多关系是指数据库表中的一行可以和另一行有多个父级关联或子级关联，这种关系可以在同一个表中创建。在本文中，我们将介绍在MySQL中创建同一表多对多关系的方法以及如何在查询时使用它。

阅读更多：MySQL 教程

建立同一表多对多关系

要建立同一表多对多关系，我们需要创建一个中间表，该中间表将用于存储与该表中的每个行相关的所有父级和子级信息。让我们看一个示例：

假设我们有一个名为“employees”的员工表，每个员工拥有多个下属，并且每个员工都是另一个员工的下属。我们可以使用以下MySQL语句创建一个员工表：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    supervisor_id INT REFERENCES employees(id)
);

在此表中，每个员工的顶级领导者将不会有任何上级，而下属的顶级领导者将在“supervisor_id”列中指向他们的上级。如果员工没有下属，则该列将为空。

要创建同一表多对多关系，我们需要创建一个中间表。我们可以使用以下语句创建一个名为“employee_subordinate”中间表：

CREATE TABLE employee_subordinate (
    employee_id INT REFERENCES employees(id),
    subordinate_id INT REFERENCES employees(id),
    PRIMARY KEY (employee_id, subordinate_id)
);

此中间表的“employee_id”列存储每个员工的ID，“subordinate_id”列存储该员工的下属ID。联合主键（即第三行）确保每个员工和下属组合都是唯一的。

为了将员工与他们的下属关联起来，我们可以使用以下INSERT语句将记录插入employee_subordinate表：

INSERT INTO employee_subordinate (employee_id, subordinate_id)
VALUES
    (1, 2),
    (1, 3),
    (2, 4),
    (2, 5),
    (3, 6),
    (3, 7);

这将创建以下关系：

Employee 1 -> Employee 2
Employee 1 -> Employee 3
Employee 2 -> Employee 4
Employee 2 -> Employee 5
Employee 3 -> Employee 6
Employee 3 -> Employee 7

现在，我们已经在MySQL中创建了一个同一表多对多关系的中间表，让我们探讨如何使用它。

在查询中使用同一表多对多关系

使用同一表多对多关系时，我们可以通过多次连接相同的表来获取所需信息。让我们看一个示例：

我们想要找出Bob的所有下属，并递归地找出下属的下属。我们可以使用以下查询：

SELECT e1.name AS e1_name, e2.name AS e2_name, e3.name AS e3_name
FROM employees AS e1
LEFT JOIN employee_subordinate AS es1 ON e1.id = es1.employee_id
LEFT JOIN employees AS e2 ON e2.id = es1.subordinate_id
LEFT JOIN employee_subordinate AS es2 ON e2.id = es2.employee_id
LEFT JOIN employees AS e3 ON e3.id = es2.subordinate_id
WHERE e1.name = 'Bob';

这将返回以下结果：

e1_name	e2_name	e3_name
Bob	Alice	Charlie
Bob	Alice	David
Bob	Alice	Eve
Bob	Cathy	Frank
Bob	Cathy	George

此查询使用表“employee_subordinate”连接表“employees”三次，每个连接都到同一个表，以获取所需信息。

请注意，此查询尚未递归地获取每个下属的下属，因此结果中仅显示了第一级下属的名称。要递归地获取每个下属的下属，我们可以使用MySQL中的递归查询。以下是一个使用递归查询获取每个下属的下属的示例查询：

WITH RECURSIVE emp_hierarchy AS (
    SELECT e1.id, e1.name, e2.id AS superior_id, e2.name AS superior_name
    FROM employees AS e1
    LEFT JOIN employee_subordinate AS es ON e1.id = es.subordinate_id
    LEFT JOIN employees AS e2 ON es.employee_id = e2.id
    WHERE e1.name = 'Bob'
    UNION ALL
    SELECT e.id, e.name, eh.superior_id, eh.superior_name
    FROM employees AS e
    INNER JOIN emp_hierarchy AS eh ON e.id = eh.id
)
SELECT *
FROM emp_hierarchy;

这将返回带有每个下属ID，名称和其上级ID和名称的结果集。使用地方的递归查询，我们遍历了员工表中的所有下属，并将它们连接到它们的上级。请注意，此查询的第一个SELECT语句不是递归查询的一部分，而是返回Bob及其下属与其上级名称的初始结果集。