MySQL之子查询

你的名字 2022-05-24 03:24 255阅读 0赞

了解子查询的最好方法就是看例子

现在我们来看看orders表和orderitems表

orders表  
![这里写图片描述][SouthEast]

orderitems表  
![这里写图片描述][SouthEast 1]

现在我想要查找所有订购物品id为TNT2的客户，怎么查？  
orderitems里有prod\_id  
orders表里有cust\_id  
但这两项在两张表里，怎么查？  
我们说，关系表关系表，两张表中肯定有关联的项，一比较，发现都有一个order\_num列。下面是检索思路

*  通过orderitems表由prod\_id=TNT2检索出匹配的order\_num
 *  通过orders表由order\_num匹配出对应的cust\_id

思路有了，下面就是敲代码

SELECT order_num
    FROM orderitems
    WHERE prod_id = "TNT2";

![这里写图片描述][SouthEast 2]

得到符合条件的两单为20005和20007，现在就是找下这两单的顾客

SELECT cust_id
    FROM orders
    WHERE order_num IN (20005, 20007);

![这里写图片描述][SouthEast 3]

以上便是直接的实现过程，下面就用子查询的方式来写，其实就是嵌套

SELECT cust_id
    FROM orders
    WHERE order_num IN (SELECT order_num
                        FROM orderitems
                        WHERE prod_id = "TNT2");

![这里写图片描述][SouthEast 4]

再做一个练习。如果我想得到每个客户的订单总数，该怎么写

客户表(里面有cust\_id)  
![这里写图片描述][SouthEast 5]

订单表(里面也有cust\_id)  
![这里写图片描述][SouthEast 6]

要想得出所有顾客的订单数，我们先想想一个顾客的订单数怎么写

SELECT cust_id, COUNT(*) AS cust_order
    FROM orders
    WHERE cust_id = 10001;

![这里写图片描述][SouthEast 7]

现在推广到所有客户的订单数呢？

SELECT cust_name,
           (SELECT COUNT(*)
           FROM orders
           WHERE orders.cust_id = customers.cust_id) AS orders
    FROM customers;

![这里写图片描述][SouthEast 8]

好好想一下上面的思路，首先我们最终需要的是一张cust\_name和orders的表，cust\_name由customers表提供，写在最外面，另外一个列orders表需要用计算字段COUNT()来汇总，而且是对每一个顾客进行订单汇总。这里用WHERE来选择条件。**由于orders表和customers表都有cust\_id，因此我们用完全限定列名。**

[SouthEast]: /images/20220524/2ccdf280e0bc4bc4a244fe4bb670e13d.png
[SouthEast 1]: /images/20220524/d161736f4b9f435580f82ca1449a538e.png
[SouthEast 2]: /images/20220524/073f8ac0e5f040beb8d6112e54e107ae.png
[SouthEast 3]: /images/20220524/eb3eeadcfb0449f39ec1f57b3c4c7c3a.png
[SouthEast 4]: /images/20220524/cdc594d35cb94f2bba2020d6ab54e4ee.png
[SouthEast 5]: /images/20220524/e63228c2a090435d8f48ccd6012f9606.png
[SouthEast 6]: /images/20220524/19c26ed91bab4eba870c216668a3a9e7.png
[SouthEast 7]: /images/20220524/65454b27ad2c4c93b13b1a392e24853a.png
[SouthEast 8]: /images/20220524/193d5d0ce0fd430291ada3e87f2c2f8e.png