11.1 子查询

查询（query），任何SQL语句都是查询。但此术语一般指SELECT语句。

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

作为子查询的SELECT语句只能查询单个列，否则会返回错误。

11.2 利用子查询进行过滤

在SELECT语句中，子查询总是从内向外处理。

MariaDB [sqlbzbh]> 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 in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> SELECT * FROM Orders;

+-----------+---------------------+------------+

| order_num | order_date          | cust_id    |

+-----------+---------------------+------------+

|     20005 | 2012-05-01 00:00:00 | 1000000001 |

|     20006 | 2012-01-12 00:00:00 | 1000000003 |

|     20007 | 2012-01-30 00:00:00 | 1000000004 |

|     20008 | 2012-02-03 00:00:00 | 1000000005 |

|     20009 | 2012-02-08 00:00:00 | 1000000001 |

+-----------+---------------------+------------+

5 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

+-----------+

| order_num |

+-----------+

|     20007 |

|     20008 |

+-----------+

2 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (20007,20008);

+------------+

| cust_id    |

+------------+

| 1000000004 |

| 1000000005 |

+------------+

2 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

+------------+

| cust_id    |

+------------+

| 1000000004 |

| 1000000005 |

+------------+

2 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> 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     | NULL                  |

| 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         | NULL                  |

+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

5 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN ('1000000004','1000000005');

+---------------+--------------------+

| cust_name     | cust_contact       |

+---------------+--------------------+

| Fun4All       | Denise L. Stephens |

| The Toy Store | Kim Howard         |

+---------------+--------------------+

2 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

MariaDB [sqlbzbh]> 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         |

+---------------+--------------------+

2 rows in set (0.00 sec)

MariaDB [sqlbzbh]>

11.3 作为计算字段使用子查询

在这种构造中，必须严格限制有歧义的列。可通过完全限定列名的方法实现。

完全限定列名，就是同时指定表名和列名，并用一个句点分隔。

推荐的方式：如果SELECT语句中操作多个表，使用完全限定列名来避免歧义。

MariaDB [sqlbzbh]> 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 in set (0.00 sec)

MariaDB [sqlbzbh]>

巴特西

读书笔记--SQL必知必会11--使用子查询

11.1 子查询

11.2 利用子查询进行过滤

11.3 作为计算字段使用子查询

最新文章

热门文章