本文实例讲述了mysql派生表(Derived Table)简单用法。分享给大家供大家参考,具体如下:
关于这个派生表啊,我们首先得知道,派生表是从select语句返回的虚拟表。派生表类似于临时表,但是在SELECT语句中使用派生表比临时表简单得多,因为它不需要创建临时表的步骤。所以当SELECT语句的FROM子句中使用独立子查询时,我们将其称为派生表。废话不多说,我们来具体的解释:
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;
这条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 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);
上面#号部分是咱们之前的那条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;
咱们来看下结果集的实例:
+----------------+--------+---------------+ | 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;
具体是啥意思,相信聪明如大家肯定比我有更好的理解了,咱就不赘述了。完事来看下结果集:
+---------------+------------+ | customerGroup | groupCount | +---------------+------------+ | Gold | 61 | | Platinum | 4 | | Silver | 8 | +---------------+------------+ 3 rows in set
得嘞,咱就到这里了。
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]