子查询返回一个表
最后,第三种选择是当子查询返回整个表时。这是最常见的选项。
很多时候我们想要稍微调整某个表的情况。然后才将更正后的表与另一个表连接(使用 JOIN ON 运算符)。
让我们从最简单的情况开始,我们用 JOIN 连接两个表:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
您可能还记得,任务表中有一些任务没有分配给任何人:employee_id是NULL。
让我们生成一个更正后的表,我们将所有挂起的任务分配给主管(他的 ID = 4)。
为此,我们使用IFNULL()函数:
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
这个查询的结果是:
ID | 员工ID | 姓名 | 最后期限 |
---|---|---|---|
1个 | 1个 | 修复一个前端bug | 2022-06-01 |
2个 | 2个 | 修复后端的一个bug | 2022-06-15 |
3个 | 5个 | 买咖啡 | 2022-07-01 |
4个 | 5个 | 买咖啡 | 2022-08-01 |
5个 | 5个 | 买咖啡 | 2022-09-01 |
6个 | 4个 | 打扫办公室 | (无效的) |
7 | 4个 | 享受生活 | (无效的) |
8个 | 6个 | 享受生活 | (无效的) |
更正后的单元格标记为红色。
现在让我们将更正后的表替换到查询中:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
而不是任务表。
这样的请求看起来像这样:
SELECT * FROM employee e JOIN (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
FROM task
) t ON e.id = t.emploee_id
我们没有使用单词task ,而是写了括号并将请求正文放在其中。
顺便说一句,嵌套查询的别名 t(别名)非常有用。嵌套查询与表不同,它没有自己的名称,因此别名非常不合适。
这是这样一个查询的结果:
ID | 姓名 | 职业 | 薪水 | 年龄 | 加入日期 | ID | 员工ID | 姓名 |
---|---|---|---|---|---|---|---|---|
1个 | 伊万诺夫伊万 | 程序员 | 100000 | 25 | 2012-06-30 | 1个 | 1个 | 修复一个前端bug |
2个 | 彼得罗夫彼得 | 程序员 | 80000 | 23 | 2013-08-12 | 2个 | 2个 | 修复后端的一个bug |
4个 | 拉比诺维奇·莫伊沙 | 导演 | 200000 | 35 | 2015-05-12 | 6个 | 4个 | 打扫办公室 |
4个 | 拉比诺维奇·莫伊沙 | 导演 | 200000 | 35 | 2015-05-12 | 7 | 4个 | 享受生活 |
5个 | 基连科阿纳斯塔西娅 | 办公室主管 | 40000 | 25 | 2015-10-10 | 4个 | 5个 | 买咖啡 |
5个 | 基连科阿纳斯塔西娅 | 办公室主管 | 40000 | 25 | 2015-10-10 | 5个 | 5个 | 买咖啡 |
5个 | 基连科阿纳斯塔西娅 | 办公室主管 | 40000 | 25 | 2015-10-10 | 3个 | 5个 | 买咖啡 |
6个 | 瓦斯卡 | 猫 | 1000 | 3个 | 2018-11-11 | 8个 | 6个 | 享受生活 |
我们的主管有“打扫办公室”的任务,我想他会很快找人委托给它的:)使用WITH语句
顺便说一下,从 MySQL 版本 8 开始,您不再需要将所有子查询都放在最终查询中。它们可以单独执行。为此,使用WITH 语句。
它允许您创建一个虚拟表(命名查询),它的外观由一个模板给出:
WITH Name AS (request)
很多时候,您的子查询具有未命名的列,例如 COUNT(*),您没有为其分配唯一的名称。在这种情况下,WITH 语句可以选择为子查询指定新的列名。
它的第二种形式由模板给出:
WITH Name(column1, column2, …) AS (request)
您可以根据需要使用任意数量的虚拟表(命名查询)并在其中相互引用。您的请求的一般形式将是这样的:
WITH name1 AS (request1),
name2 AS (request2),
name3 AS (request3)
SELECT * FROM name1 JOIN name2 ON …
现在让我们进行可怕的查询:
SELECT * FROM employee e JOIN (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
FROM task
) t ON e.id = t.emploee_id
并使用 WITH 语句重写它:
WITH task2(id, employee_id, name, deadline)
AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task)
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
或者您可以不使用列名,但是您必须为 IFNULL() 函数指定一个别名:
WITH task2 AS (
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
)
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id