15.子查询之from型子查询

最新推荐文章于 2024-07-15 17:29:31 发布

suelta_th

最新推荐文章于 2024-07-15 17:29:31 发布

阅读量3.6k

点赞数

分类专栏： MySQL自学文章标签： MySQL基础 from子查询 from型子查询子查询

本文链接：https://blog.csdn.net/qq_36528734/article/details/81105965

版权

MySQL自学专栏收录该内容

31 篇文章 0 订阅

订阅专栏

本节讲解from子查询及其用法

为了大家更好的理解，我将例子中需要的sql文件上传到了百度网盘，供大家食用。
链接：https://pan.baidu.com/s/1dqQS2sQj8QoCKi3UfN1wVA 密码：h7hh

from子查询

from:把内层的查询结果当成临时表，供外层查询。必须给表加上别名。
不太明白?举个栗子

需求：从products表中选出最便宜的5种商品，5种商品价格从高到低排序
怎么办？分两个步骤（为了便于理解，其实看步骤2就是from型子查询）
1. 按从低到高取出最便宜的5种商品。
2. 将步骤1的这5种商品逆序。（配合from子查询）

mysql> # 按从低到高取出最便宜的5种商品
mysql> select * from products order by prod_price limit 5;
+---------+---------+---------------+------------+-----------------------------------------------+
| prod_id | vend_id | prod_name     | prod_price | prod_desc                                     |
+---------+---------+---------------+------------+-----------------------------------------------+
| FC      |    1003 | Carrots       |       2.50 | Carrots (rabbit hunting season only)          |
| TNT1    |    1003 | TNT (1 stick) |       2.50 | TNT, red, single stick                        |
| FU1     |    1002 | Fuses         |       3.42 | 1 dozen, extra long                           |
| SLING   |    1003 | Sling         |       4.49 | Sling, one size fits all                      |
| ANV01   |    1001 | .5 ton anvil  |       5.99 | .5 ton anvil, black, complete with handy hook |
+---------+---------+---------------+------------+-----------------------------------------------+
5 rows in set (0.41 sec)

mysql> #将步骤1的这5种商品逆序（配合from子查询）
mysql> select * from (select * from products order by prod_price limit 5) as temp order by prod_price desc;
+---------+---------+---------------+------------+-----------------------------------------------+
| prod_id | vend_id | prod_name     | prod_price | prod_desc                                     |
+---------+---------+---------------+------------+-----------------------------------------------+
| ANV01   |    1001 | .5 ton anvil  |       5.99 | .5 ton anvil, black, complete with handy hook |
| SLING   |    1003 | Sling         |       4.49 | Sling, one size fits all                      |
| FU1     |    1002 | Fuses         |       3.42 | 1 dozen, extra long                           |
| FC      |    1003 | Carrots       |       2.50 | Carrots (rabbit hunting season only)          |
| TNT1    |    1003 | TNT (1 stick) |       2.50 | TNT, red, single stick                        |
+---------+---------+---------------+------------+-----------------------------------------------+
5 rows in set (0.84 sec)

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录