子查詢返回一個表
最後,第三種選擇是當子查詢返回整個表時。這是最常見的選項。
很多時候我們想要稍微調整某個表的情況。然後才將更正後的表與另一個表連接(使用 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
GO TO FULL VERSION