子查询之from型子查询实例讲解

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

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)