MySQL - 派生表

MySQL：派生表和公共表表达式（CTE）

什么是派生表？

派生表是一种虚拟表，它是在另一个查询的 FROM 子句中通过 SELECT 语句生成的。它本质上是一个子查询，你可以在该单个查询的执行期间立即将其用作实际表。它不存储在数据库中，只在语句执行期间存在。

派生表对于将复杂问题分解为更小、更逻辑化的步骤非常有用，例如执行聚合然后将结果联接到另一个表。

先决条件：示例数据

在我们的示例中，假设我们有一个 sales 表。

CREATE TABLE sales (
    order_id INT PRIMARY KEY,
    employee_id INT NOT NULL,
    region VARCHAR(50),
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
(1, 101, 'North', 1500.00),
(2, 102, 'South', 2200.00),
(3, 101, 'North', 800.00),
(4, 103, 'West', 3100.00),
(5, 102, 'South', 1800.00),
(6, 101, 'West', 2500.00);

基本语法和用法

派生表必须使用 AS 关键字给定一个别名，以便外部查询可以引用它。

SELECT ...
FROM (
    -- 这是创建派生表的子查询
    SELECT ... FROM ...
) AS derived_table_alias;

示例：计算每个区域的平均销售额

让我们找出每个区域的平均销售额。首先，我们将创建一个派生表来计算每个区域的总销售额，然后外部查询将计算这些总额的平均值。

SELECT AVG(regional_total) AS average_of_regional_sales
FROM (
    SELECT region, SUM(sale_amount) AS regional_total
    FROM sales
    GROUP BY region
) AS region_sales;

输出

average_of_regional_sales
3200.000000

派生表中的过滤和别名

你可以像处理普通表一样处理派生表，应用 WHERE 子句、JOIN 操作以及为其列设置别名。

示例：查找总销售额超过 $3000 的员工

SELECT
    employee_id,
    total_sales
FROM (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
) AS employee_summary -- 派生表的别名
WHERE total_sales > 3000.00;

输出

employee_id	total_sales
101	4800.00
102	4000.00
103	3100.00

现代替代方案：公共表表达式（CTE）

虽然派生表有效，但包含多个或嵌套派生表的复杂查询可能会变得非常难以阅读。MySQL 8.0 及更高版本支持公共表表达式（CTE），它提供了一种更清晰、更易读的方式来实现相同的结果。CTE 是一个命名的临时结果集，你可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中引用它。

语法

WITH cte_name AS (
    -- 定义 CTE 的子查询
    SELECT ... FROM ...
)
SELECT ... FROM cte_name;

示例：使用 CTE 重写员工销售查询

让我们使用 CTE 重写上一个示例。请注意逻辑是如何分离的：我们首先定义 employee_summary，然后从它中进行选择。

WITH employee_summary AS (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT
    employee_id,
    total_sales
FROM employee_summary
WHERE total_sales > 3000.00;

输出是相同的，但查询无疑更易于阅读和维护。

CTE 与派生表：如何选择？

• 可读性： CTE 几乎总是更具可读性，尤其对于复杂查询。它们允许你按顺序构建逻辑。
• 可重用性： 一个 CTE 可以在同一个查询中多次引用，而派生表则需要再次定义。
• 递归： CTE 支持递归查询，允许你处理分层数据（如组织结构图或零件装配），这是派生表无法实现的。
• 建议： 对于任何现代 MySQL 开发（8.0+ 版本），优先使用 CTE 而非派生表。它们功能更强大，可读性更强，代表了编写复杂 SQL 的现代标准。

客户端程序的实用示例

以下是你可以从各种编程语言执行使用 CTE 的查询的方法。我们将使用员工销售示例。

Node.js (async/await)
Python (mysql-connector)
Java (JDBC)
PHP (PDO)



此示例使用 `mysql2/promise` 执行 CTE 查询。

```javascript
// main.js
import mysql from 'mysql2/promise';

async function getTopEmployees() {
  let connection;
  const sql = `
    WITH employee_summary AS (
      SELECT employee_id, SUM(sale_amount) AS total_sales
      FROM sales
      GROUP BY employee_id
    )
    SELECT employee_id, total_sales
    FROM employee_summary
    WHERE total_sales > ?
    ORDER BY total_sales DESC;
  `;

  try {
    connection = await mysql.createConnection({ /* connection config */ }); // 连接配置
    const [rows] = await connection.execute(sql, [3000]);

    console.log('Top Performing Employees (Sales > 3000):'); // 销售额超过 3000 的顶尖员工：
    rows.forEach(row => {
      console.log(`  Employee ID: ${row.employee_id}, Total Sales: ${row.total_sales}`); // 员工ID：${row.employee_id}，总销售额：${row.total_sales}
    });
  } finally {
    if (connection) await connection.end();
  }
}
getTopEmployees();

此 Python 示例使用多行字符串定义 CTE 查询，并带参数执行。

main.py

import mysql.connector

config = { 'user': 'root', 'password': 'password', 'database': 'your_database' }
sql = """
WITH employee_summary AS (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM employee_summary
WHERE total_sales > %s
ORDER BY total_sales DESC
"""

with mysql.connector.connect(**config) as cnx:
    with cnx.cursor(dictionary=True) as cursor:
        cursor.execute(sql, (3000,))
        print('Top Performing Employees (Sales > 3000):') // 销售额超过 3000 的顶尖员工：
        for row in cursor:
            print(f"  Employee ID: {row['employee_id']}, Total Sales: {row['total_sales']}") // 员工ID：{row['employee_id']}，总销售额：{row['total_sales']}

此现代 Java 示例使用 PreparedStatement 执行 CTE 查询。

Main.java

import java.sql.*;

public class MysqlCteExample {
    // ... connection details ... (连接详情)
    public static void main(String[] args) {
        String sql = """
            WITH employee_summary AS (
                SELECT employee_id, SUM(sale_amount) AS total_sales
                FROM sales
                GROUP BY employee_id
            )
            SELECT employee_id, total_sales
            FROM employee_summary
            WHERE total_sales > ?
            ORDER BY total_sales DESC
        """
;
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setBigDecimal(1, new java.math.BigDecimal("3000.00"));

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Top Performing Employees (Sales > 3000):"); // 销售额超过 3000 的顶尖员工：
                while (rs.next()) {
                    System.out.printf("  Employee ID: %d, Total Sales: %.2f\n", // 员工ID：%d，总销售额：%.2f
                        rs.getInt("employee_id"),
                        rs.getBigDecimal("total_sales"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

此 PHP 示例使用 PDO 执行 CTE 查询。

<?php
require 'config.php'; // Assumes a file with PDO connection $pdo (假设文件包含 PDO 连接 $pdo)

$sql = "
    WITH employee_summary AS (
      SELECT employee_id, SUM(sale_amount) AS total_sales
      FROM sales
      GROUP BY employee_id
    )
    SELECT employee_id, total_sales
    FROM employee_summary
    WHERE total_sales > :min_sales
    ORDER BY total_sales DESC;
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['min_sales' => 3000]);

$employees = $stmt->fetchAll();

echo "Top Performing Employees (Sales > 3000):\n"; // 销售额超过 3000 的顶尖员工：
foreach ($employees as $employee) {
    echo sprintf("  Employee ID: %d, Total Sales: %s\n", // 员工ID：%d，总销售额：%s
        $employee['employee_id'],
        $employee['total_sales']
    );
}
?>