mysql派⽣系统_mysql派⽣表（DerivedTable）简单⽤法实例
解析
本⽂实例讲述了mysql派⽣表(Derived Table)简单⽤法。分享给⼤家供⼤家参考，具体如下：
关于这个派⽣表啊，我们⾸先得知道，派⽣表是从select语句返回的虚拟表。派⽣表类似于临时表，但是在SELECT语句中使⽤派⽣表⽐临时表简单得多，因为它不需要创建临时表的步骤。所以当SELECT语句的FROM⼦句中使⽤独⽴⼦查询时，我们将其称为派⽣表。废话不多说，我们来具体的解释：
SELECT
column_list
FROM
* (SELECT
* column_list
* FROM
* table_1) derived_table_name;
WHERE derived_lumn > 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
这条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
# 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的为银牌客户
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 |
+----------------+--------+---------------+
完事嘞，咱们就可以使⽤上⾯的查询所得的表作为派⽣表来进⾏关联查询并且进⾏分组，获取想要的数据了，咱们来看下⾯的sql感受⼀
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;
具体是啥意思，相信聪明如⼤家肯定⽐我有更好的理解了，咱就不赘述了。完事来看下结果集：
mysql存储过程使用+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold | 61 |
| Platinum | 4 |
| Silver | 8 |
+---------------+------------+
3 rows in set
得嘞，咱就到这⾥了。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧⼤全》、《MySQL常⽤函数⼤汇总》、《MySQL⽇志操作技巧⼤全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧⼤全》及《MySQL数据库锁相关技巧汇总》
希望本⽂所述对⼤家MySQL数据库计有所帮助。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论