如何利用Self Join自連接實現多對多關系

在數據庫設計中，多對多關系通常通過一個關聯表（也稱為連接表或交叉表）來實現。然而，如果你想使用自連接（Self Join）來實現多對多關系，可以通過創建一個具有遞歸關系的表來實現。這種方法通常用于表示層次結構數據，例如組織結構、分類層次等。

以下是一個示例，展示如何使用自連接來實現一個簡單的多對多關系。假設我們有一個員工表，每個員工可以屬于多個部門，每個部門也可以有多個員工。

表結構

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE EmployeeDepartment (
    EmployeeID INT,
    DepartmentID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

使用自連接實現多對多關系

假設我們想查詢每個員工所屬的所有部門，并且每個部門的所有員工。我們可以使用自連接來實現這一點。

查詢每個員工所屬的所有部門

SELECT 
    e.EmployeeID,
    e.EmployeeName,
    d.DepartmentID,
    d.DepartmentName
FROM 
    Employees e
JOIN 
    EmployeeDepartment ed ON e.EmployeeID = ed.EmployeeID
JOIN 
    Departments d ON ed.DepartmentID = d.DepartmentID;

查詢每個部門的所有員工

SELECT 
    d.DepartmentID,
    d.DepartmentName,
    e.EmployeeID,
    e.EmployeeName
FROM 
    Departments d
JOIN 
    EmployeeDepartment ed ON d.DepartmentID = ed.DepartmentID
JOIN 
    Employees e ON ed.EmployeeID = e.EmployeeID;

遞歸自連接實現層次結構

如果你想表示一個員工的直接上級和下屬關系，可以使用遞歸自連接。例如，假設我們有一個員工表，其中包含員工的直接上級信息。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

查詢每個員工的直接上級和下屬

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        1 AS Level
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL -- 根節點

    UNION ALL

    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    eh.EmployeeID,
    eh.EmployeeName,
    eh.Level,
    m.EmployeeID AS ManagerID,
    m.EmployeeName AS ManagerName
FROM 
    EmployeeHierarchy eh
LEFT JOIN 
    Employees m ON eh.ManagerID = m.EmployeeID
ORDER BY 
    eh.Level, eh.EmployeeID;

在這個查詢中，我們使用遞歸CTE（Common Table Expression）來構建員工層次結構，并查詢每個員工的直接上級和下屬。

通過這種方式，你可以使用自連接來實現多對多關系，特別是當你需要表示層次結構數據時。