子查询

mysql> select * from goods;
+----------+--------+----------------------------------------+
| goods_id | cat_id | goods_name                             |
+----------+--------+----------------------------------------+
|        1 |      4 | KD876                                  |
|        4 |      8 | 诺基亚N85原装充电器                    |
|        3 |      8 | 诺基亚原装5800耳机                     |
|        5 |     11 | 索爱原装M2卡读卡器                     |
|        6 |     11 | 胜创KINGMAX内存卡                      |
|        7 |      8 | 诺基亚N85原装立体声耳机HS-82           |
|        8 |      3 | 飞利浦9@9v                             |
|        9 |      3 | 诺基亚E66                              |
|       10 |      3 | 索爱C702c                              |
|       11 |      3 | 索爱C702c                              |
|       12 |      3 | 摩托罗拉A810                           |
|       13 |      3 | 诺基亚5320 XpressMusic                 |
|       14 |      4 | 诺基亚5800XM                           |
|       15 |      3 | 摩托罗拉A810                           |
|       16 |      2 | 恒基伟业G101                           |
|       17 |      3 | 夏新N7                                 |
|       18 |      4 | 夏新T5                                 |
|       19 |      3 | 三星SGH-F258                           |
|       20 |      3 | 三星BC01                               |
|       21 |      3 | 金立A30                                |
|       22 |      3 | 多普达Touch HD                         |
|       23 |      5 | 诺基亚N96                              |
|       24 |      3 | P806                                   |
|       25 |     13 | 小灵通/固话50元充值卡                  |
|       26 |     13 | 小灵通/固话20元充值卡                  |
|       27 |     15 | 联通100元充值卡                        |
|       28 |     15 | 联通50元充值卡                         |
|       29 |     14 | 移动100元充值卡                        |
|       30 |     14 | 移动20元充值卡                         |
|       31 |      3 | 摩托罗拉E8                             |
|       32 |      3 | 诺基亚N85                              |
|       33 |      4 | 金立910浪漫镶砖手机                    |
+----------+--------+----------------------------------------+
32 rows in set (0.02 sec)

1. where型子查询

内层sql的返回值在where后作为条件表达式的一部分

mysql> select goods_id, cat_id, goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);
+----------+--------+----------------------------------------+
| goods_id | cat_id | goods_name                             |
+----------+--------+----------------------------------------+
|        6 |     11 | 胜创KINGMAX内存卡                      |
|        7 |      8 | 诺基亚N85原装立体声耳机HS-82           |
|       16 |      2 | 恒基伟业G101                           |
|       23 |      5 | 诺基亚N96                              |
|       26 |     13 | 小灵通/固话20元充值卡                  |
|       28 |     15 | 联通50元充值卡                         |
|       30 |     14 | 移动20元充值卡                         |
|       32 |      3 | 诺基亚N85                              |
|       33 |      4 | 金立910浪漫镶砖手机                    |
+----------+--------+----------------------------------------+
9 rows in set (0.01 sec)

2. from型子查询

内层sql查询结果,作为一张表,供外层的sql语句再次查询

mysql> select tmp.goodsId, tmp.catId, tmp.goodsName from
(select goods_id as goodsId, cat_id as catId, goods_name as goodsName from goods where goods_id in (select max(goods_id) from goods group by cat_id))
as tmp where tmp.goodsName like '%充值%';
+---------+-------+--------------------------------+
| goodsId | catId | goodsName                      |
+---------+-------+--------------------------------+
|      26 |    13 | 小灵通/固话20元充值卡          |
|      28 |    15 | 联通50元充值卡                 |
|      30 |    14 | 移动20元充值卡                 |
+---------+-------+--------------------------------+
3 rows in set (0.00 sec)