MySQL 派生表

---

简介：在本教程中，您将了解MySQL派生表以及如何简化复杂查询。

---

MySQL派生表简介

派生表是从SELECT语句返回的虚拟表。派生表类似于临时表，但在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。 术语派生表和子查询通常可互换使用。当在SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。 以下说明了使用派生表的查询：

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

Every derived table must have its own alias.

以下说明了使用派生表的SQL语句：

SELECT 
    column_list
FROM
    (SELECT 
        column_list
    FROM
        table_1) derived_table_name;
WHERE derived_table_name.c1 > 0;

---

一个简单的MySQL派生表实例

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

+----------------+
| orders         |
+----------------+
| orderNumber    |
| orderDate      |
| requiredDate   |
| shippedDate    |
| status         |
| comments       |
| customerNumber |
+----------------+
7 rows in set (0.01 sec)

+-----------------+
| orderdetails    |
+-----------------+
| orderNumber     |
| productCode     |
| quantityOrdered |
| priceEach       |
| orderLineNumber |
+-----------------+
5 rows in set (0.00 sec)

SELECT 
    productCode, 
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2013
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

运行结果：

+-------------+--------+
| productCode | sales  |
+-------------+--------+
| S18_3232    | 103480 |
| S10_1949    |  67985 |
| S12_1108    |  59852 |
| S12_3891    |  57403 |
| S12_1099    |  56462 |
+-------------+--------+
5 rows in set (0.00 sec)

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

+--------------------+
| products           |
+--------------------+
| productCode        |
| productName        |
| productLine        |
| productScale       |
| productVendor      |
| productDescription |
| quantityInStock    |
| buyPrice           |
| MSRP               |
+--------------------+
9 rows in set (0.01 sec)

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2013
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2013
INNER JOIN
    products USING (productCode);

以下显示了上述查询的输出：

+-----------------------------+--------+
| productName                 | sales  |
+-----------------------------+--------+
| 1992 Ferrari 360 Spider red | 103480 |
| 1952 Alpine Renault 1300    |  67985 |
| 2001 Ferrari Enzo           |  59852 |
| 1969 Ford Falcon            |  57403 |
| 1968 Ford Mustang           |  56462 |
+-----------------------------+--------+
5 rows in set (0.00 sec)

在这个例子中：

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

---

一个更复杂的MySQL派生表实例

假设你在2013年的客户分为3组：  platinum，gold，和silver。此外，您需要知道每个组中的客户数量，并满足以下条件：

1. 订单量大于100K的白金客户
2. 订购量在10K到100K之间的黄金客户
3. 订单量小于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) = 2013
GROUP BY customerNumber;

以下是查询的输出：

+----------------+--------+---------------+
| customerNumber | sales  | customerGroup |
+----------------+--------+---------------+
|            103 |  14571 | Gold          |
|            112 |  32642 | Gold          |
|            114 |  53429 | Gold          |
|            121 |  51710 | Gold          |
|            124 | 167783 | Platinum      |
|            128 |  34651 | Gold          |
|            129 |  40462 | Gold          |
|            131 |  22293 | Gold          |
|            141 | 189840 | Platinum      |
|            144 |   7675 | Silver        |
...

然后，您可以将此查询用作派生表并执行分组，如下所示：

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) = 2013
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

查询返回客户组和每个客户组中的客户数。

+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold          |         61 |
| Platinum      |          4 |
| Silver        |          8 |
+---------------+------------+
3 rows in set (0.01 sec)

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

---