学习SQL系列之查询数据(三)

子查询

子查询过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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 |
+------------+
| 1000000004 |
| 1000000005 |
+------------+

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

+------------+
| cust_id |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 |
+---------------+--------------------+

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

注意:

  • 子查询的语句只能返回一个列数据
  • 太多层的子查询会有性能问题

子查询作为计算字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT cust_id, COUNT(*) AS orders FROM Orders WHERE cust_id = '1000000001';

+------------+--------+
| cust_id | orders |
+------------+--------+
| 1000000001 | 2 |
+------------+--------+

SELECT cust_id, cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers;

+------------+---------------+------------+--------+
| cust_id | cust_name | cust_state | orders |
+------------+---------------+------------+--------+
| 1000000001 | Village Toys | MI | 2 |
| 1000000002 | Kids Place | OH | 0 |
| 1000000003 | Fun4All | IN | 1 |
| 1000000004 | Fun4All | AZ | 1 |
| 1000000005 | The Toy Store | IL | 1 |
+------------+---------------+------------+--------+

说明

  • 本文所有例子都来自SQL IN 10 MINUTES
  • 本文的所有例子都是基于MySQL,其它数据库可能会有微小差别,具体请查阅特定数据库的文档