An Essential Guide to MySQL Derived Tables

摘要：在本教程中，您将了解 MySQL 派生表以及如何使用它们来简化复杂的查询。

MySQL派生表简介

派生表是从SELECT语句返回的虚拟表。派生表与临时表类似，但在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表。

术语派生表和子查询通常可以互换使用。当独立子查询用在SELECT语句的FROM子句中时，它也称为派生表。

下面说明了使用派生表的查询：

请注意，独立子查询是可以独立于外部查询执行的子查询。

与子查询不同，派生表必须有一个别名，以便您稍后可以在查询中引用其名称。如果派生表没有别名，MySQL 将发出以下错误：

Every derived table must have its own alias.

下面说明了使用派生表的查询的语法：

SELECT 
    select_list
FROM
    (SELECT 
        select_list
    FROM
        table_1) derived_table_name
WHERE 
    derived_table_name.c1 > 0;Code language: SQL (Structured Query Language) (sql)

一个简单的 MySQL 派生表示例

以下查询从示例数据库中的orders和orderdetails表中获取2003 年销售收入排名前五的产品：

SELECT 
    productCode, 
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

您可以使用此查询的结果作为派生表并将其与products表连接，如下所示：

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);
Code language: SQL (Structured Query Language) (sql)

下面显示了上面查询的输出：

在这个例子中：

1. 首先，执行子查询来创建结果集或派生表。
2. 然后，执行外部查询，使用productCode列将top5product2003派生表与products表连接起来。

更复杂的 MySQL 派生表示例

假设您必须将 2003 年购买产品的客户分为 3 组： platinum 、 gold和silver 。并且您需要知道满足以下条件的每个组中的客户数量：

• 订单量大于10万的白金客户。
• 订单量在10K到100K之间的黄金客户。
• 订单量小于10K的白银客户。

要形成此查询，您首先需要使用CASE表达式和GROUP BY子句将每个客户放入各自的组中，如下所示：

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;
Code language: SQL (Structured Query Language) (sql)

以下是查询的输出：

然后，您可以使用该查询作为派生表并进行分组，如下所示：

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;    
Code language: SQL (Structured Query Language) (sql)

该查询返回客户组以及每个组中的客户数量。

在本教程中，您学习了如何使用 MySQL 派生表（ FROM子句中的子查询）来简化复杂查询。