mysql派生表（Derived Table）简单用法实例解析
本文实例讲述了mysql派生表（DerivedTable）简单用法。分享给大家供大家参考，具体如下：
关于这个派生表啊，我们首先得知道，派生表是从select语句返回的虚拟表。派生表类似于临时表，但是在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。所以当SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。废话不多说，我们来具体的解释：
SELECT column_list FROM *(SELECT *column_list *FROM *table_1)derived_table_name; WHEREderived_table_name.column>1...
其中标记星号的地方就使用了派生表。为了详细点，咱们来看个具体的例子。咱们接下来要从数据库中的orders表和orderdetails表中获得2018年销售收入最高的前5名产品。先来看下表的字段：
咱们先来看下面这条sql：
SELECT productCode, ROUND(SUM(quantityOrdered*priceEach))sales FROM orderdetails INNERJOIN ordersUSING(orderNumber) WHERE YEAR(shippedDate)=2018 GROUPBYproductCode ORDERBYsalesDESC LIMIT5;
这条sql是以两张表中共有的orderNumber字段为联合查询的节点，完事之后，以时间为条件，再以那个什么productCode字段为分组依据，完事获取分组字段和计算之后的别称字段，再以sales字段为排序依据，最后提取前五条结果。大概就是这么回事，完事结果集我们可以看做是一张临时表或者别的什么。大家来看个结果集：
+-------------+--------+ |productCode|sales| +-------------+--------+ |S18_3232|103480| |S10_1949|67985| |S12_1108|59852| |S12_3891|57403| |S12_1099|56462| +-------------+--------+ 5rowsinset
完事呢，既然是学习派生表，我们当然可以使用此查询的结果作为派生表，并将其与products表相关联。其中，products表的结构如下所示：
mysql>descproducts; +--------------------+---------------+------+-----+---------+-------+ |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|| +--------------------+---------------+------+-----+---------+-------+ 20rowsinset
表结构既然了解完事了，我们就来看下面的sql：
SELECT productName,sales FROM #(SELECT #productCode, #ROUND(SUM(quantityOrdered*priceEach))sales #FROM #orderdetails #INNERJOINordersUSING(orderNumber) #WHERE #YEAR(shippedDate)=2018 #GROUPBYproductCode #ORDERBYsalesDESC #LIMIT5)top5_products_2018 INNERJOIN productsUSING(productCode);
上面#号部分是咱们之前的那条sql，方便大家理解，我使用#标记了出来，大家写的时候可不能用啊。完事我们来看下这条sql是神马意思呢？它是把我们用#标记的部分当做一个表，来做一个简单的联合查询而已。然而这个表，我们就叫它派生表，它会在使用过后即时清除的，所以我们在简化复杂查询的时候可以考虑使用。废话不多说，我们来看下结果集：
+-----------------------------+--------+ |productName|sales| +-----------------------------+--------+ |1992Ferrari360Spiderred|103480| |1952AlpineRenault1300|67985| |2001FerrariEnzo|59852| |1969FordFalcon|57403| |1968FordMustang|56462| +-----------------------------+--------+ 5rowsinset
然后呢，咱们再来简单总结下：
- 首先，执行子查询来创建一个结果集或派生表。
- 然后，在productCode列上使用products表连接top5_products_2018派生表的外部查询。
完事呢，简单的派生表的理解和使用就到这里了。咱们再来一个稍稍复杂的来尝尝味道哈，首先假设必须将2018年的客户分为3组：铂金，白金和白银。此外，需要了解每个组中的客户数量，具体情况如下：
- 订单总额大于100000的为铂金客户；
- 订单总额为10000至100000的为黄金客户
- 订单总额为小于10000的为银牌客户
要构建此查询，首先，我们需要使用case表达式和groupby子句将每个客户放入相应的分组中，如下所示：
SELECT customerNumber, ROUND(SUM(quantityOrdered*priceEach))sales, (CASE WHENSUM(quantityOrdered*priceEach)<10000THEN'Silver' WHENSUM(quantityOrdered*priceEach)BETWEEN10000AND100000THEN'Gold' WHENSUM(quantityOrdered*priceEach)>100000THEN'Platinum' END)customerGroup FROM orderdetails INNERJOIN ordersUSING(orderNumber) WHERE YEAR(shippedDate)=2018 GROUPBYcustomerNumber ORDERBYsalesDESC;
咱们来看下结果集的实例：
+----------------+--------+---------------+ |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)ASgroupCount FROM (SELECT customerNumber, ROUND(SUM(quantityOrdered*priceEach))sales, (CASE WHENSUM(quantityOrdered*priceEach)<10000THEN'Silver' WHENSUM(quantityOrdered*priceEach)BETWEEN10000AND100000THEN'Gold' WHENSUM(quantityOrdered*priceEach)>100000THEN'Platinum' END)customerGroup FROM orderdetails INNERJOINordersUSING(orderNumber) WHERE YEAR(shippedDate)=2018 GROUPBYcustomerNumber)cg GROUPBYcg.customerGroup;
具体是啥意思，相信聪明如大家肯定比我有更好的理解了，咱就不赘述了。完事来看下结果集：
+---------------+------------+ |customerGroup|groupCount| +---------------+------------+ |Gold|61| |Platinum|4| |Silver|8| +---------------+------------+ 3rowsinset
得嘞，咱就到这里了。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。