MySQL - 自连接

MySQL: 自连接

目录

- 什么是自连接？
- 经典示例：员工与经理
- 带 ORDER BY 子句的自连接
- 在客户端应用程序中执行自连接

什么是自连接？

自连接是一种常规的连接，但它不是连接两个不同的表，而是将一个表与其自身连接。这在表包含层次结构数据或记录引用同一表内其他记录时非常有用。

要执行自连接，您必须使用表别名在查询中为该表提供两个不同的临时名称。这允许数据库将其视为两个独立的表，使您能够比较同一表中不同行的列。

最佳实践是使用显式的 JOIN ... ON 语法，它比旧的、隐式的逗号分隔语法更清晰、更标准。

现代语法

SELECT
    alias1.column_name,
    alias2.column_name
FROM
    table_name AS alias1
JOIN
    table_name AS alias2 ON alias1.common_field = alias2.related_field;

经典示例：员工与经理

自连接最常见的用例是查询一个 employees 表，其中每个员工记录可能有一个 manager_id，指向同一表中的另一个员工的 id。

首先，让我们创建并填充 employees 表：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    manager_id INT NULL, -- 对于顶级员工（例如 CEO），可以为 NULL
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
) ENGINE=InnoDB;

现在，让我们插入一些数据。注意，CEO (Jane Smith) 的 manager_id 为 NULL。

INSERT INTO employees (employee_id, first_name, last_name, manager_id) VALUES
(1, 'Jane', 'Smith', NULL),
(2, 'John', 'Doe', 1),
(3, 'Peter', 'Jones', 1),
(4, 'Mary', 'Williams', 2),
(5, 'David', 'Brown', 2);

该表现在看起来像这样：

employee_id	first_name	last_name	manager_id
1	Jane	Smith	NULL
2	John	Doe	1
3	Peter	Jones	1
4	Mary	Williams	2
5	David	Brown	2

让我们使用自连接来列出每位员工及其经理的姓名。我们将使用别名 e 代表员工，m 代表经理。

这里我们使用 LEFT JOIN，因为我们想包含没有经理的 CEO。常规的 INNER JOIN 会将其排除。

SELECT
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM
    employees AS e
LEFT JOIN
    employees AS m ON e.manager_id = m.employee_id;

输出

结果清楚地展示了员工与经理之间的关系：

employee_first_name	employee_last_name	manager_first_name	manager_last_name
Jane	Smith	NULL	NULL
John	Doe	Jane	Smith
Peter	Jones	Jane	Smith
Mary	Williams	John	Doe
David	Brown	John	Doe

带 ORDER BY 子句的自连接

您可以添加 ORDER BY 子句来对结果进行排序，使其更易于阅读。让我们按照经理姓名，然后按照员工姓名对之前的查询结果进行排序。

SELECT
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    CONCAT(m.first_name, ' ', m.last_name) AS manager_name
FROM
    employees AS e
LEFT JOIN
    employees AS m ON e.manager_id = m.employee_id
ORDER BY
    manager_name ASC, employee_last_name ASC;

输出

排序后的结果更加整洁：

employee_first_name	employee_last_name	manager_name
Jane	Smith	NULL
John	Doe	Jane Smith
Peter	Jones	Jane Smith
David	Brown	John Doe
Mary	Williams	John Doe

在客户端应用程序中执行自连接

从客户端程序执行自连接与执行任何其他查询相同。以下是演示这一点的现代示例。

Python
Node.js
PHP
Java



**设置：** `pip install mysql-connector-python`

```python
import mysql.connector

db_config = { 'host': 'localhost', 'user': 'user', 'password': 'password', 'database': 'your_db' }

query = """
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;
"""

try:
    with mysql.connector.connect(**db_config) as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(query)
            for row in cursor.fetchall():
                print(f"Employee: {row['employee_first_name']} {row['employee_last_name']}, Manager: {row['manager_name'] or 'N/A'}")
except mysql.connector.Error as e:
    print(f"Error: {e}")

设置： npm install mysql2

const mysql = require('mysql2/promise');

const dbConfig = { host: 'localhost', user: 'user', password: 'password', database: 'your_db' };

const query = `
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;`;

async function getEmployeeHierarchy() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(query);
        rows.forEach(row => {
            console.log(`Employee: ${row.employee_first_name} ${row.employee_last_name}, Manager: ${row.manager_name || 'N/A'}`);
        });
    } catch (error) {
        console.error(`Error: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

getEmployeeHierarchy();

设置： 使用 Composer。

<?php
$dbConfig = ['host' => 'localhost', 'user' => 'user', 'password' => 'password', 'database' => 'your_db'];

$query = "
    SELECT
        e.first_name AS employee_first_name,
        e.last_name AS employee_last_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
    FROM
        employees AS e
    LEFT JOIN
        employees AS m ON e.manager_id = m.employee_id
    ORDER BY
        manager_name, employee_last_name;";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli($dbConfig['host'], $dbConfig['user'], $dbConfig['password'], $dbConfig['database']);
    $result = $mysqli->query($query);
    while ($row = $result->fetch_assoc()) {
        $manager = $row['manager_name'] ?? 'N/A';
        echo "Employee: {$row['employee_first_name']} {$row['employee_last_name']}, Manager: {$manager}\n";
    }
} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

?>

设置： 将 MySQL JDBC 驱动程序添加到您的项目。

import java.sql.*;

public class SelfJoinExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "user";
        String password = "password";

        String query = """
            SELECT
                e.first_name AS employee_first_name,
                e.last_name AS employee_last_name,
                CONCAT(m.first_name, ' ', m.last_name) AS manager_name
            FROM
                employees AS e
            LEFT JOIN
                employees AS m ON e.manager_id = m.employee_id
            ORDER BY
                manager_name, employee_last_name;""";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                String managerName = rs.getString("manager_name");
                System.out.printf("Employee: %s %s, Manager: %s%n",
                        rs.getString("employee_first_name"),
                        rs.getString("employee_last_name"),
                        (managerName == null) ? "N/A" : managerName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}