MySQL中的嵌套查詢

更新時間：2024年04月10日 09:22:21 作者：Q小白養(yǎng)成記

這篇文章主要介紹了MySQL中的嵌套查詢方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

嵌套查詢

是指在一個完整的查詢語句之中，包含若干個不同功能的小查詢；從而一起完成復雜查詢的一種編寫形式。

包含的查詢放在（）里，包含的查詢出現(xiàn)的位置：

位置	含義
SELECT之后	把查詢結果作為表頭使用
FROM之后	把查詢結果作為表使用
WHERE之后	把查詢結果作為查詢條件
HAVING之后	把查詢結果作為過濾使用

SELECT之后

查看客戶的總訂單數(shù)

mysql> select cust_name,
    -> (select count(*) from orders
    -> where orders.cust_id = customers.cust_id )
    -> as orders_num from customers;
+----------------+------------+
| cust_name      | orders_num |
+----------------+------------+
| Coyote Inc.    |          2 |
| Mouse House    |          0 |
| Wascals        |          1 |
| Yosemite Place |          1 |
| E Fudd         |          1 |
+----------------+------------+
5 rows in set (0.01 sec)

WHERE之后

查詢下單了TNT2的客戶id

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.01 sec)

實驗

1.使用子查詢，返回購買價格為10或以上的商品的客戶列表。您需，然后使用要使用Orderltems表查找匹配的訂單號(order num)Orders表檢索每個匹配訂單的客戶ID(cust id)。

mysql> select distinct cust_id from  orders
    -> where order_num in 
    -> ( select order_num from orderitems 
    -> where item_price >= 10 ) ;
+---------+
| cust_id |
+---------+
|   10001 |
|   10003 |
|   10004 |
+---------+
3 rows in set (0.01 sec)

2.您需要知道訂購產(chǎn)品BR01的日期。編寫一條SQL語句，使用子查詢確定哪些訂單(Orderltems中)購買了prod_id為BR01的商品，然后返回客戶ID(cust_id)，和訂單日期(order_date)。按訂單日期排序結果。

mysql> select order_date from orders 
    -> where order_num in
    -> ( select order_num from orderitems
    -> where prod_id = 'ANV01');
+---------------------+
| order_date          |
+---------------------+
| 2023-09-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

3.更新前面的挑戰(zhàn)，為購買了prod id為AVN01的商品的任何客戶返回客戶電子郵件(Customers表中的custemail)。這里有一個提示:這涉及到SELECT語句，最里面的查詢從Orderltems返回order num，中間的查詢從Customers返回custid。

mysql> select cust_email from customers
    -> where cust_id in
    -> ( select cust_id from orders
    -> where order_num in
    -> ( select order_num from orderitems
    -> where prod_id = 'ANV01' ) );
+-----------------+
| cust_email      |
+-----------------+
| ylee@coyote.com |
+-----------------+
1 row in set (0.00 sec)

4.您需要一個包含每個客戶訂購的總額的客戶ID列表。編寫一條SOL語句，返回客戶ID(0rders表中的cust id)和total ordered，并使用一個子查詢返回每個客戶的訂單總數(shù)。按花費從大到小的順序排列結果。這里有一個提示:你已經(jīng)使用SUM()來計算訂單總計。

mysql> SELECT cust_id,
    -> ( SELECT SUM(quantity * item_price )  FROM orderitems
    -> WHERE order_num IN
    -> ( SELECT order_num  FROM orders
    -> WHERE orders.cust_id = customers.cust_id ) ) 
    -> AS total_ordered
    -> FROM customers
    -> ORDER BY total_ordered DESC;
+---------+---------------+
| cust_id | total_ordered |
+---------+---------------+
|   10004 |       1000.00 |
|   10001 |        188.34 |
|   10005 |        125.00 |
|   10003 |         55.00 |
|   10002 |          NULL |
+---------+---------------+
5 rows in set (0.00 sec)

5.編寫一條SQL語句，從Products表中檢索所有產(chǎn)品名稱(prodname)，以及一個名為quant_sold的計算列，該列包含此商品的銷售總數(shù)(使用Orderltems表中的子查詢和SUM(quantity)檢索)。

mysql> SELECT prod_name,
    -> (SELECT Sum(quantity)
    -> FROM orderitems
    -> WHERE products.prod_id=orderitems.prod_id) 
    -> AS quant_sold
    -> FROM products;
+----------------+------------+
| prod_name      | quant_sold |
+----------------+------------+
| .5 ton anvil   |         10 |
| 1 ton anvil    |          3 |
| 2 ton anvil    |          1 |
| Detonator      |       NULL |
| Bird seed      |          2 |
| Carrots        |         50 |
| Fuses          |       NULL |
| JetPack 1000   |       NULL |
| JetPack 2000   |          1 |
| Oil can        |          1 |
| Safe           |       NULL |
| Sling          |          1 |
| TNT (1 stick)  |       NULL |
| TNT (5 sticks) |        105 |
+----------------+------------+
14 rows in set (0.00 sec)