7. 使用子查询

本节使用的表

orderitems

postgres=# \d orderitems

                  Table "public.orderitems"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_item | integer       |           | not null |
 prod_id    | character(10) |           | not null |
 quantity   | integer       |           | not null |
 item_price | numeric(8,2)  |           | not null |
Indexes:
    "orderitems_pkey" PRIMARY KEY, btree (order_num, order_item)
Foreign-key constraints:
    "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)
    "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)

SELECT
    *
FROM
    orderitems;

 order_num | order_item |  prod_id   | quantity | item_price
-----------+------------+------------+----------+------------
     20005 |          1 | BR01       |      100 |       5.49
     20005 |          2 | BR03       |      100 |      10.99
     20006 |          1 | BR01       |       20 |       5.99
     20006 |          2 | BR02       |       10 |       8.99
     20006 |          3 | BR03       |       10 |      11.99
     20007 |          1 | BR03       |       50 |      11.49
     20007 |          2 | BNBG01     |      100 |       2.99
     20007 |          3 | BNBG02     |      100 |       2.99
     20007 |          4 | BNBG03     |      100 |       2.99
     20007 |          5 | RGAN01     |       50 |       4.49
     20008 |          1 | RGAN01     |        5 |       4.99
     20008 |          2 | BR03       |        5 |      11.99
     20008 |          3 | BNBG01     |       10 |       3.49
     20008 |          4 | BNBG02     |       10 |       3.49
     20008 |          5 | BNBG03     |       10 |       3.49
     20009 |          1 | BNBG01     |      250 |       2.49
     20009 |          2 | BNBG02     |      250 |       2.49
     20009 |          3 | BNBG03     |      250 |       2.49
(18 rows)

orders

postgres=# \d orders

                    Table "public.orders"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_date | date          |           | not null |
 cust_id    | character(10) |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_num)
Foreign-key constraints:
    "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
Referenced by:
    TABLE "orderitems" CONSTRAINT "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)

SELECT
    *
FROM
    orders;

 order_num | order_date |  cust_id
-----------+------------+------------
     20005 | 2012-05-01 | 1000000001
     20006 | 2012-01-12 | 1000000003
     20007 | 2012-01-30 | 1000000004
     20008 | 2012-02-03 | 1000000005
     20009 | 2012-02-08 | 1000000001
(5 rows)

customers

postgres=# \d customers

                    Table "public.customers"
    Column    |      Type      | Collation | Nullable | Default
--------------+----------------+-----------+----------+---------
 cust_id      | character(10)  |           | not null |
 cust_name    | character(50)  |           | not null |
 cust_address | character(50)  |           |          |
 cust_city    | character(50)  |           |          |
 cust_state   | character(5)   |           |          |
 cust_zip     | character(10)  |           |          |
 cust_country | character(50)  |           |          |
 cust_contact | character(50)  |           |          |
 cust_email   | character(255) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (cust_id)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

SELECT
    *
FROM
    customers;

 cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact
   | cust_email
------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------
---+----------------------------
 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith
   | sales@villagetoys.com
 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green
   |
 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones
   | jjones@fun4all.com
 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens
   | dstephens@fun4all.com
 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard
   |
(5 rows)

利用子查询进行过滤

SELECT
    order_num
FROM
    orderitems
WHERE
    prod_id = 'RGAN01';

 order_num
-----------
     20007
     20008
(2 rows)

SELECT
    cust_id
FROM
    orders
WHERE
    order_num in (20007, 20008);

  cust_id
------------
 1000000004
 1000000005
(2 rows)

SELECT
    cust_id 
FROM
    orders 
WHERE
    order_num IN (
        SELECT
            order_num  
        FROM
            orderitems  
        WHERE
            prod_id = 'RGAN01' 
    );

  cust_id
------------
 1000000004
 1000000005
(2 rows)

注意：作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

作为计算字段使用子查询

SELECT
    cust_name,
    cust_state,
    cust_id
FROM
    customers ;

                     cust_name                      | cust_state |  cust_id
----------------------------------------------------+------------+------------
 Village Toys                                       | MI         | 1000000001
 Kids Place                                         | OH         | 1000000002
 Fun4All                                            | IN         | 1000000003
 Fun4All                                            | AZ         | 1000000004
 The Toy Store                                      | IL         | 1000000005
(5 rows)

SELECT
    *
FROM
    orders;

 order_num | order_date |  cust_id
-----------+------------+------------
     20005 | 2012-05-01 | 1000000001
     20006 | 2012-01-12 | 1000000003
     20007 | 2012-01-30 | 1000000004
     20008 | 2012-02-03 | 1000000005
     20009 | 2012-02-08 | 1000000001
(5 rows)

下面这条子查询对检索出的每个顾客执行一次，比较orders表中的cust_id和当前正从customers表中检索的cust_id。在此例中，该子查询执行了5次。

SELECT
    cust_name,
    cust_state,
    (
        SELECT
            COUNT(*)   
        FROM
            orders   
        WHERE
            orders.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
 The Toy Store                                      | IL         |      1
 Village Toys                                       | MI         |      2
(5 rows)