如何在mysql中使用派生表

53次阅读

共计 4600 个字符，预计需要花费 12 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章将为大家详细讲解有关如何在 mysql 中使用派生表，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

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

SELECT 
 column_list
* (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
 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

表结构既然了解完事了，我们就来看下面的 sql：

SELECT 
 productName, sales
# (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);

上面 #号部分是咱们之前的那条 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
 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 |
+----------------+--------+---------------+

完事嘞，咱们就可以使用上面的查询所得的表作为派生表来进行关联查询并且进行分组，获取想要的数据了，咱们来看下面的 sql 感受一下：

SELECT 
 customerGroup, 
 COUNT(cg.customerGroup) AS groupCount
 (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

关于如何在 mysql 中使用派生表就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

向 AI 问一下细节

正文完