gaodaima.com
MySQL学习足迹记录12–使用子查询
1.子查询（subquery):即嵌套在其他查询中的查询
原始数据如下：
mysql> SELECT order_num FROM orderitems;+-----------+| order_num |+-----------+| 20005 || 20005 || 20009 || 20005 || 20009 || 20008 || 20006 || 20009 || 20009 || 20005 || 20007 |+-----------+11 rows in set (0.01 sec)mysql> SELECT cust_id FROM orders;+---------+| cust_id |+---------+| 10001 || 10001 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.01 sec)现在先分步查询step1: mysql> SELECT order_num -> FROM orderitems -> WHERE prod_id = 'TNT2';+-----------+| order_num |+-----------+| 20005 || 20007 |+-----------+2 rows in set (0.00 sec)step2: mysql> SELECT cust_id FROM orders -> WHERE order_num IN( 20005,20007);+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.00 sec) Step3: 使用子查询把step1,step2组合起来（即把20005,20007换掉） mysql> SELECT cust_id -> FROM orders -> WHERE order_num IN( SELECT order_num -> FROM orderitems -> WHERE prod_id = 'TNT2');+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.00 sec)TIPS： 在SELECT语句中，子查询总是从内向外处理的。 子查询可以嵌套多重 step4: mysql> SELECT cust_name,cust_contact -> FROM customers -> WHERE cust_id IN (10001,10004); #（10001，10004）既是step3查询的结果+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+2 rows in set (0.01 sec)step5:把step4的IN (10001,10004)换成子查询 mysql> SELECT cust_name,cust_contact -> FROM customers -> WHERE cust_id IN (SELECT cust_id -> FROM orders -> WHERE order_num IN (SELECT order_num -> FROM orderitems -> WHERE prod_id = 'TNT2'));+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+2 rows in set (0.00 sec)
2.计算字段使用子查询
原始数据
mysql> SELECT cust_id FROM orders;+---------+| cust_id |+---------+| 10001 || 10001 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.01 sec)mysql> SELECT cust_id FROM customers;+---------+| cust_id |+---------+| 10001 || 10002 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.00 sec)mysql> SELECT cust_id,(SELECT COUNT(*) FROM orders -> WHERE orders.cust_id = customers.cust_id) AS orders -> FROM customers -> ORDER BY cust_id;+---------+--------+ | cust_id | orders |+---------+--------+| 10001 | 2 || 10002 | 0 || 10003 | 1 || <b>本文来源gao@!dai!ma.com搞$$代^@码5网@</b>10004 | 1 || 10005 | 1 |+---------+--------+5 rows in set (0.00 sec)
TIPS：
子查询最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列
gaodaima.com
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL学习足迹记录12–使用子查询_MySQL
转载请注明原文链接：MySQL学习足迹记录12–使用子查询_MySQL
