mysql 生成虚拟表格 mysql虚拟表使用

转载

蓝色忧郁花 2024-04-09 08:59:57

文章标签 mysql 生成虚拟表格 mysql 派生表（Derived Table） Code 文章分类 MySQL 数据库

关于这个派生表啊，我们首先得知道，派生表是从select语句返回的虚拟表。派生表类似于临时表，但是在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。所以当SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。废话不多说，我们来具体的解释：

SELECT 
    column_list
FROM
*    (SELECT 
*        column_list
*    FROM
*        table_1) derived_table_name;
WHERE derived_table_name.column > 1...
SELECT 
    column_list
FROM
*    (SELECT 
*        column_list
*    FROM
*        table_1) derived_table_name;
WHERE derived_table_name.column > 1...

其中标记星号的地方就使用了派生表。为了详细点，咱们来看个具体的例子。咱们接下来要从数据库中的orders表和orderdetails表中获得2018年销售收入最高的前5名产品。先来看下表的字段：

咱们先来看下面这条sql：

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

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

这条sql是以两张表中共有的orderNumber字段为联合查询的节点，完事之后，以时间为条件，再以那个什么productCode字段为分组依据，完事获取分组字段和计算之后的别称字段，再以sales字段为排序依据，最后提取前五条结果。大概就是这么回事，完事结果集我们可以看做是一张临时表或者别的什么。大家来看个结果集：

+-------------+--------+
| 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    |       |
+--------------------+---------------+------+-----+---------+-------+
20 rows in set
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    |       |
+--------------------+---------------+------+-----+---------+-------+
20 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) = 2018
#    GROUP BY productCode
#    ORDER BY sales DESC
#    LIMIT 5) top5_products_2018
INNER JOIN

SELECT 
    productName, sales
FROM
#    (SELECT 

#        productCode, 

#        ROUND(SUM(quantityOrdered * priceEach)) sales


#    FROM

#        orderdetails


#    INNER JOIN orders USING (orderNumber)

#    WHERE


#        YEAR(shippedDate) = 2018

#    GROUP BY productCode


#    ORDER BY sales DESC

#    LIMIT 5) top5_products_2018


INNER JOIN

products USING (productCode);

    products USING (productCode);

上面#号部分是咱们之前的那条sql，方便大家理解，我使用#标记了出来，大家写的时候可不能用啊。完事我们来看下这条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表连接top5_products_2018派生表的外部查询。

完事呢，简单的派生表的理解和使用就到这里了。咱们再来一个稍稍复杂的来尝尝味道哈，首先假设必须将2018年的客户分为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) = 2018
GROUP BY customerNumber 
ORDER BY sales DESC;

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) = 2018
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          |
| ************此处省略了many数据 *********|
|            219 | 4466   | Silver        |
|            323 | 2880   | Silver        |
|            381 | 2756   | Silver        |
+----------------+--------+---------------+

+----------------+--------+---------------+
| customerNumber | sales  | customerGroup |
+----------------+--------+---------------+
|            141 | 189840 | Platinum      |
|            124 | 167783 | Platinum      |
|            148 | 150123 | Platinum      |
|            151 | 117635 | Platinum      |
|            320 | 93565  | Gold          |
|            278 | 89876  | Gold          |
|            161 | 89419  | Gold          |
| ************此处省略了many数据 *********|
|            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) = 2018
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

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

具体是啥意思，相信聪明如大家肯定比我有更好的理解了，咱就不赘述了。完事来看下结果集：

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

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

得嘞，咱就到这里了。如果感觉不错的话，请多多点赞支持哦。。。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。