MySQL派生表 - tw511教學網

在本教學中，您將了解和學習MySQL派生表以及如何簡化複雜查詢。

1. 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;

2. 簡單的MySQL派生表範例

以下查詢從範例資料庫(yiibaidb)中的orders表和orderdetails表中獲得2013年銷售收入最高的前5名產品：

參考以下查詢語句 -

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

您可以使用此查詢的結果作為派生表，並將其與products表相關聯，products表的結構如下所示：

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

參考以下查詢語句 -

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

在上面這個例子中：

首先，執行子查詢來建立一個結果集或派生表。
然後，在productCode列上使用products表連線top5product2013派生表的外部查詢。

3. 一個更複雜的MySQL派生表範例

假設必須將2013年的客戶分為3組：鉑金，白金和白銀。此外，需要了解每個組中的客戶數量，具體情況如下：

訂單總額大於100000的為鉑金客戶；
訂單總額為10000至100000的為黃金客戶
訂單總額為小於10000的為銀牌客戶

要構建此查詢，首先，您需要使用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 
ORDER BY sales DESC;

以下是查詢的輸出：

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

然後，可以使用此查詢作為派生表，並按如下所示進行分組：

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

在本教學中，您已經學會了如何使用FROM子句中的子查詢作為MySQL派生表來簡化複雜查詢。