> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-6/pai-sheng-biao.md).

# 7.2 派生表

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

## 1. MySQL派生表介绍

派生表是从[SELECT语句](http://www.yiibai.com/mysql/select-statement-query-data.html)返回的虚拟表。派生表类似于临时表，但是在`SELECT`语句中使用派生表比[临时表](http://www.yiibai.com/mysql/temporary-table.html)简单得多，因为它不需要创建临时表的步骤。

**术语:\***派生表\*和[子查询](http://www.yiibai.com/mysql/subquery.html)通常可互换使用。当`SELECT`语句的`FROM`子句中使用独立子查询时，我们将其称为派生表。

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

![img](/files/-LyNv-2Ax9nFDlE8V3-V)

> 请注意，独立子查询是一个子查询，可独立于包含该语句的执行语句。

与子查询不同，派生表必须具有[别名](http://www.yiibai.com/mysql/alias.html)，以便稍后在查询中引用其名称。 如果派生表没有别名，MySQL将发出以下错误：

```sql
Every derived table must have its own alias.
```

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

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

## 2. 简单的MySQL派生表示例

以下查询从[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`orders`表和`orderdetails`表中获得`2013`年销售收入最高的前`5`名产品：

![img](/files/-LyNv-2CvRfhfv8wd3IF)

参考以下查询语句 -

```sql
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;
```

执行上面查询语句，得到以下结果 -

```sql
+-------------+--------+
| productCode | sales  |
+-------------+--------+
| S18_3232    | 103480 |
| S10_1949    | 67985  |
| S12_1108    | 59852  |
| S12_3891    | 57403  |
| S12_1099    | 56462  |
+-------------+--------+
5 rows in set
```

您可以使用此查询的结果作为派生表，并将其与`products`表相关联，`products`表的结构如下所示：

```sql
mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI |         |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set
```

参考以下查询语句 -

```sql
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);
```

执行上面查询语句，得到以下结果 -

```sql
+-----------------------------+--------+
| 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
```

在上面这个例子中：

* 首先，执行子查询来创建一个结果集或派生表。
* 然后，在`productCode`列上使用`products`表连接`top5product2013`派生表的外部查询。

## 3. 一个更复杂的MySQL派生表示例

假设必须将`2013`年的客户分为`3`组：铂金，白金和白银。 此外，需要了解每个组中的客户数量，具体情况如下：

* 订单总额大于`100000`的为铂金客户；
* 订单总额为`10000`至`100000`的为黄金客户
* 订单总额为小于`10000`的为银牌客户

要构建此查询，首先，您需要使用[CASE](http://www.yiibai.com/mysql/case-function.html)表达式和[GROUP BY](http://www.yiibai.com/mysql/group-by.html)子句将每个客户放入相应的分组中，如下所示：

```sql
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 
ORDER BY sales DESC;
```

以下是查询的输出：

```sql
+----------------+--------+---------------+
| customerNumber | sales  | customerGroup |
+----------------+--------+---------------+
|            141 | 189840 | Platinum      |
|            124 | 167783 | Platinum      |
|            148 | 150123 | Platinum      |
|            151 | 117635 | Platinum      |
|            320 | 93565  | Gold          |
|            278 | 89876  | Gold          |
|            161 | 89419  | Gold          |
| ************此处省略了一大波数据 *********|
|            219 | 4466   | Silver        |
|            323 | 2880   | Silver        |
|            381 | 2756   | Silver        |
+----------------+--------+---------------+
```

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

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

执行上面查询语句，得到以下结果 -

```sql
+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold          |         61 |
| Platinum      |          4 |
| Silver        |          8 |
+---------------+------------+
3 rows in set
```

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