在本教程中，我们将学习MySQL中的子查询。

子查询只是查询中的查询。

数据表

在本教程中，我们将使用"员工"，"订单"和"评论"表。

表：员工

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     7 | 1900-01-01 | 2016-02-04 06:08:10 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     8 | 1900-02-03 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     9 | 1900-05-20 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     6 | 1900-10-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

表：订单

mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified        | created             |
+---------+------------+--------+-------------+---------------------+---------------------+
|       1 | e03        |  15.00 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       2 | e01        |  25.50 | OPEN        | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       3 | e05        | 100.70 | CLOSED      | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
|       4 | e02        |  22.18 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       5 | e04        |   9.50 | CANCELLED   | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       6 | e04        |  99.99 | OPEN        | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)

表：注释

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2016-01-02 01:02:03 | 2016-01-02 01:02:03 |
|         3 | e01        | Happy               | 2016-01-02 01:04:03 | 2016-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

例子1

在下面的示例中，我们将列出至少评论过一次的员工的详细信息。

为了显示员工的详细信息，我们可以使用SELECT查询并显示诸如employeeid，firstname和lastname之类的列。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e;

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        |      |   |
| e02        | John      | Doe      |
| e03        | Jane      | Doe      |
| e04        | Tin       | Tin      |
| e05        | Bob       | Coder    |
+------------+-----------+----------+
5 rows in set (0.00 sec)

因此，我们从employee表中获取了雇员的详细信息。

现在，我们将在WHERE子句中编写子查询，以仅获取评论的那些员工。

在以下查询中，我们使用IN运算符。
点击这里了解更多。

我们也在使用" SELECT DISTINCT"，这将给我们带来独特的价值。

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid IN (SELECT DISTINCT
            c.employeeid
        FROM
            comments c);

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        |      |   |
| e03        | Jane      | Doe      |
| e04        | Tin       | Tin      |
+------------+-----------+----------+
3 rows in set (0.00 sec)

因此，我们仅列出至少评论过一次的员工的详细信息。

例子2

在以下示例中，我们将显示员工的详细信息，评论总数和OPEN订单总数。

假设我们要在结果集中显示employeeid，名字，姓氏，total_comment和total_open_order。

为了计算total_comment和total_open_order的值，我们将编写两个子查询。

子查询" total_comment"如下。

mysql> SELECT 
    COUNT(c.commentid)
FROM
    comments c
WHERE
    c.employeeid = e.employeeid;

其中，" e"是"员工"表的别名。

子查询" total_open_order"如下。

mysql> SELECT 
    COUNT(o.orderid)
FROM
    orders o
WHERE
    o.employeeid = e.employeeid;

因此，具有两个子查询以获得所需结果的最终查询如下。

mysql> SELECT 
    e.employeeid,
    e.firstname,
    e.lastname,
    (SELECT 
            COUNT(c.commentid)
        FROM
            comments c
        WHERE
            c.employeeid = e.employeeid) AS 'total_comment',
    (SELECT 
            COUNT(o.orderid)
        FROM
            orders o
        WHERE
            o.employeeid = e.employeeid) AS 'total_open_order'
FROM
    employee e;

+------------+-----------+----------+---------------+------------------+
| employeeid | firstname | lastname | total_comment | total_open_order |
+------------+-----------+----------+---------------+------------------+
| e01        |      |   |             2 |                1 |
| e02        | John      | Doe      |             0 |                1 |
| e03        | Jane      | Doe      |             2 |                1 |
| e04        | Tin       | Tin      |             1 |                2 |
| e05        | Bob       | Coder    |             0 |                1 |
+------------+-----------+----------+---------------+------------------+
5 rows in set (0.00 sec)