需求

列出订购物品RGAN01的所有顾客

步骤

1. 检索包含物品RGAN01的所有订单的编号
2. 检索具有上一步列出的订单编号的所有顾客的ID
3. 检索前一步返回的所有顾客ID的顾客信息

   SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';
   /*
   order_num
   20007
   20008
   */

   SELECT cust_id FROM Orders WHERE order_num IN(20007,20008);
   /*
   cust_id
   10000000004
   10000000005
   */
   

   SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN(10000000004,10000000005);
   /*
   cust_name                    cust_contact
   Fun4All                        Denise L. Stephens
   The Toy Store            Kim Howard
   */
   

   使用子查询语句

   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 = 'RGAN01'));
   /*
   cust_name                    cust_contact
   Fun4All                        Denise L. Stephens
   The Toy Store            Kim Howard
   */
   

   作为计算字段使用子查询

   SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Order.cust_id = Customers.cust_id) AS Orders FROM Customers ORDER BY cust_name;
   /*
   cust_name                    cust_state                orders
   Fun4ALL                        IN                                1
   Fun4ALL                        AZ                                1
   Kids Place                OH                                0
   ...
   */
   

• SELECT COUNT(*)对表中的行进行计数