大家好！👋
好久沒在這裡發文了。過去這幾天，我重新從基礎開始複習 SQL——不只是解決問題，而是理解為什麼會有不同的 SQL 概念，以及什麼時候該使用它們。
其中讓我（以及許多初學者）感到困惑的一個主題就是關聯子查詢（correlated subqueries）。
你可能看過這樣的查詢：
SELECT ...
FROM table1 t1
WHERE EXISTS (
SELECT *
FROM table2 t2
WHERE t2.id = t1.id
);
Enter fullscreen mode Exit fullscreen mode
然後心裡產生疑問：
內層查詢是如何存取外層查詢的欄位？
內層查詢什麼時候執行？
這和一般的子查詢有什麼不同？
在這篇文章中，我們將透過解答 LeetCode 的一道真實 SQL 題目，來回答上述所有問題。
讀完之後，你將理解：
- ✅ 什麼是關聯子查詢
- ✅ 它們在內部如何運作
- ✅ SQL 的邏輯執行順序
- ✅ 什麼時候該使用它們
- ✅ 它們與視窗函式的比較
讓我們開始吧！
先備知識
本篇文章假設你已熟悉：
- 基本的
SELECT語句WHEREGROUP BY- 聚合函式（
MIN、MAX、COUNT等）如果你對這些已經感到舒適，那你已經準備好學習關聯子查詢了。
目錄
- 為什麼關聯子查詢這麼令人困惑？
- 從頭開始認識關聯子查詢
- 關聯子查詢 vs 非關聯子查詢
- SQL 的邏輯執行順序（以及關聯子查詢的定位）
- 以 LeetCode 3421 作為我們的實戰範例
- 在撰寫 SQL 之前...
- 我的完整註解解答
- 使用真實資料一步步走過查詢
- 效能探討：關聯子查詢 vs 視窗函式
- 結論與練習題
1. 為什麼關聯子查詢這麼令人困惑？
當我第一次學習 SQL 時，關聯子查詢感覺就像魔法。
我腦中不斷浮現這樣的問題：
- 內層查詢怎麼能存取外層查詢的欄位？
- 內層查詢什麼時候執行？
- 它只跑一次還是跑很多次？
- 為什麼我不能獨立執行它？
如果你也曾問過自己這些問題，別擔心——讀完這篇文章後，你將得到所有答案。
什麼是子查詢？
子查詢就是巢狀在另一個 SQL 語句中的 SELECT 敘述。目前看起來還不算太可怕：
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
這裡，內層查詢 (SELECT AVG(salary) FROM Employees) 只執行一次，產生單一數值（例如 55000），而外層查詢就直接使用這個數值。內層查詢不在乎外層查詢目前正在看哪一列。這是非關聯（或稱「簡單」）子查詢——它是完全獨立的，可以獨自執行。
是什麼讓子查詢變成「關聯」的？
💡 核心概念
關聯子查詢依賴外層查詢的值。
因此它無法獨立執行——它會針對外層查詢的每一列重新執行一次，並使用該列的值。
SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM Employees e2
WHERE e2.department_id = e.department_id -- 👈 參照外層列 `e`
);
Enter fullscreen mode Exit fullscreen mode
獨立閱讀內層查詢：
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
Enter fullscreen mode Exit fullscreen mode
e.department_id 在這個子查詢自己的 FROM Employees e2 中並不存在——它借用了外層查詢的欄位。這就是它的定義特徵。從心智模型來看，可以把它想像成一個帶有參數的函式：
find_avg_salary_for(department_id):
return AVG(salary) WHERE department_id = department_id
Enter fullscreen mode Exit fullscreen mode
而 SQL 會針對外層查詢碰到的每一列呼叫這個「函式」一次，帶入該列的 department_id。這就是整個概念。其餘部分只是建立在這個想法之上的裝飾。
一個簡單的心智模型
把外層查詢想像成一個 for 迴圈，而關聯子查詢就像在這個迴圈內部執行的程式碼，並使用迴圈變數：
# 這不是真正的 SQL——只是心智模型
for row in Employees:
dept_avg = AVG(salary WHERE department_id == row.department_id)
if row.salary > dept_avg:
emit(row.name, row.salary, row.department_id)
Enter fullscreen mode Exit fullscreen mode
這個心智模型正是為什麼關聯子查詢可能會變得很昂貴——因為如果資料庫無法將其最佳化，這確實接近它最終的執行方式（更多細節請見效能章節）。
小結
關聯子查詢會針對外層查詢產生的每一列（或群組）執行一次，並使用該列的值作為輸入。
| 非關聯子查詢 | 關聯子查詢 | |
|---|---|---|
| 參照外層查詢？ | ❌ 否 | ✅ 是 |
| 可以獨立執行？ | ✅ 可以 | ❌ 不可以 |
| 執行頻率 | 總共一次 | 針對外層每一列執行一次（概念上） |
| 典型用途 | 與全域值比較（例如整體平均） | 與「該列所屬群組」的數值比較（例如該部門的平均、該學生的最早日期） |
非關聯範例——「薪資高於公司整體平均的員工」：
SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
關聯範例——「薪資高於自己部門平均的員工」：
SELECT e.name FROM Employees e
WHERE e.salary > (
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
);
Enter fullscreen mode Exit fullscreen mode
結構相同，只有一個關鍵差異：WHERE e2.department_id = e.department_id 這一行。這一行就是「全域」與「依列範圍」之間的全部差別。
小結
如果子查詢可以獨自執行並回傳合理的結果，就是非關聯子查詢。如果它需要外層列的值才能有意義，就是關聯子查詢。
4. SQL 的邏輯執行順序（以及子查詢的定位）
這讓很多人困惑，讓我們釐清。雖然 SELECT 語句是從上到下書寫（SELECT → FROM → WHERE → GROUP BY → ...），但它的執行順序並非如此。MySQL（以及大多數 SQL 引擎）實際評估的邏輯順序如下：
① FROM （以及 JOIN）— 建立工作列集
↓
② WHERE — 過濾個別列
↓
③ GROUP BY — 將列分組
↓
④ HAVING — 過濾群組
↓
⑤ SELECT — 計算輸出欄位
（這裡會執行 SELECT 清單中的子查詢）
↓
⑥ ORDER BY — 排序最終結果
↓
⑦ LIMIT — 修剪結果
Enter fullscreen mode Exit fullscreen mode
位於 SELECT 清單內的關聯子查詢（例如我在解答中使用的那些）會在步驟 ⑤被評估——針對通過 FROM → WHERE → GROUP BY → HAVING 的每一列/群組執行。這是我們問題的關鍵洞見：
💡 核心概念
當
SELECT中的關聯子查詢執行時，MySQL 已經知道哪些(student_id, subject)群組存在（感謝GROUP BY）。接著子查詢針對每個群組被「詢問」一次：「針對這個特定的學生和科目，第一個分數是多少？最新的分數是多少？」
就是這樣。整個查詢實際上就是：1) 依學生+科目分組，2) 針對每個群組，問兩個額外問題。
小結
位於 SELECT 清單中的關聯子查詢會在步驟 ⑤執行——針對已經通過過濾與分組的每個群組執行一次。
5. 以 LeetCode 3421 作為我們的實戰範例：找出有進步的學生
資料表：Scores
| 欄位名稱 | 型別 |
|---|---|
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
(student_id, subject, exam_date) 為主鍵。每列代表一位學生在某個科目、某個考試日期的分數。score 介於 0 到 100 之間。
任務：找出有「進步」的學生。若學生在某科目滿足以下兩條件，則視為有進步：
- 在該科目至少參加過兩次不同日期的考試
- 該科目最新分數高於第一次分數
回傳 student_id, subject, first_score, latest_score，依 student_id、subject 升冪排序。
範例輸入：
| student_id | subject | score | exam_date |
|---|---|---|---|
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
預期輸出：
| student_id | subject | first_score | latest_score |
|---|---|---|---|
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
請注意被過濾掉的資料：
- 101 / Physics — 考了兩次，但分數下降（65 → 60）。不算進步。
- 103 / Math — 只考了一次。不符合「至少兩次不同日期」。
敘述很簡單，但核心困難在於：針對每一個 (student_id, subject) 組合，你需要回到同一個資料表，找出該群組的最小日期與最大日期，再找出對應的分數。這種「回到同一個資料表，但範圍限定在目前列所屬群組」的操作，正是關聯子查詢擅長的。
6. 在撰寫 SQL 之前...
讓我們暫時忘記 SQL。
假設你用 C++、Java 或 Python 撰寫。
你會怎麼做？大概會像這樣：
for each student
for each subject
earliest_exam
latest_exam
first_score
latest_score
if latest_score > first_score
print()
Enter fullscreen mode Exit fullscreen mode
SQL 只是另一種表達這個演算法的方式。GROUP BY 對應 for each student / for each subject 部分，而關聯子查詢則對應 earliest_exam、latest_exam、first_score、latest_score 的查詢。請把這個迴圈記在腦中——它能讓下面的查詢變得不那麼抽象。
7. 我的完整註解解答
完整查詢
WITH t AS (
SELECT
student_id,
subject,
-- 關聯子查詢 #1：取得此群組最早考試日期對應的分數
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- 巢狀關聯子查詢：找出此學生+科目的 MIN(exam_date)
SELECT MIN(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS first_score,
-- 關聯子查詢 #2：取得此群組最晚考試日期對應的分數
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- 巢狀關聯子查詢：找出此學生+科目的 MAX(exam_date)
SELECT MAX(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS latest_score
FROM Scores t
GROUP BY student_id, subject
)
SELECT *
FROM t
WHERE latest_score > first_score
ORDER BY student_id ASC, subject ASC;
Enter fullscreen mode Exit fullscreen mode
每個部分的作用
-
FROM Scores t GROUP BY student_id, subject— 將原始考試資料列壓縮成每個(student_id, subject)組合只有一列。這是我們要評估的「候選集合」。 -
first_score— 一個巢狀在另一個關聯子查詢內的關聯子查詢。內層（MIN(exam_date)範圍限定WHERE ss.student_id = t.student_id AND ss.subject = t.subject）找出目前群組t所屬的最早日期。外層再取出該確切日期的分數。 -
latest_score— 同樣概念，但使用MAX(exam_date)而非MIN(exam_date)。 -
「至少兩次不同日期」的條件 是隱含處理的：如果某學生/科目只有一個考試日期，那麼
first_score與latest_score會是相同值（同一天 → 同分數），因此latest_score > first_score為false，該列會被外層WHERE自然過濾掉。這是邏輯帶來的額外好處——不需要額外的HAVING COUNT(DISTINCT exam_date) >= 2。 -
最終的
WHERE latest_score > first_score— 真正的「是否有進步」檢查。 -
ORDER BY student_id, subject— 符合題目要求的輸出排序。
建立關聯的那一行
一切都取決於巢狀子查詢內的這對條件：
WHERE
ss.student_id = t.student_id
AND ss.subject = t.subject
Enter fullscreen mode Exit fullscreen mode
這兩個條件就是全部的魔法。它們將內層查詢的 MIN/MAX 計算綁定到外層查詢 t 的這一特定列，而不是計算整個資料表。移除它們，子查詢就不再是關聯的——它會變成「所有學生和科目的最早日期」，這完全不是我們想要的。
小結
巢狀的 MIN(exam_date) / MAX(exam_date) 子查詢之所以是關聯的，是因為它們受到外層群組的 student_id 與 subject 過濾——這將它們的範圍限定在「這個學生、這個科目」，而不是整個資料表。
8. 使用真實資料一步步走過查詢
讓我們針對學生 101、科目 Math 使用範例資料追蹤：
| exam_date | score |
|---|---|
| 2023-01-15 | 70 |
| 2023-02-15 | 85 |
外層查詢
----------------------------------
student_id = 101
subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
關聯子查詢
----------------------------------
Find MIN(exam_date)
WHERE
student_id = 101
AND subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
2023-01-15
│
▼
找出 exam_date = 2023-01-15 對應的 score
│
▼
first_score = 70
latest_score 分支走相同的路徑，只是將 MIN(exam_date) 換成 MAX(exam_date)，結果為 2023-02-15 → score = 85。
把所有步驟放在一起：
-
GROUP BY產生群組(101, Math)。 first_score的內層子查詢：針對(101, Math)計算MIN(exam_date)→2023-01-15。first_score的外層子查詢：找出exam_date = 2023-01-15且 student=101、subject=Math 的分數 →70。latest_score的內層子查詢：針對(101, Math)計算MAX(exam_date)→2023-02-15。latest_score的外層子查詢：找出exam_date = 2023-02-15的分數 →85。- 到目前為止的列：
(101, Math, 70, 85)。 - 最終過濾：是
85 > 70嗎？是 → 該列通過。
現在追蹤學生 103、科目 Math（只考了一次）：
| exam_date | score |
|---|---|
| 2023-01-15 | 90 |
- 群組
(103, Math)。 -
MIN(exam_date)→2023-01-15→first_score = 90。 -
MAX(exam_date)→2023-01-15（同一天，只有一列）→latest_score = 90。 - 最終過濾：是
90 > 90嗎？否 → 該列被刪除。這正是「至少兩次考試日期」規則免費被強制執行的方式。
以及學生 101、科目 Physics（分數從 65 下降到 60）：
-
first_score = 65，latest_score = 60。 - 是
60 > 65嗎？否 → 被刪除。
這就是整個演算法的手動追蹤。
小結
每個群組的 first_score 與 latest_score 都是透過「問同樣的兩步驟問題」獨立解析：找出邊界日期，再找出該日期的分數。
9. 效能探討：子查詢 vs 視窗函式
MySQL 8.0+ 提供了視窗函式，能以更有效率且更易讀的方式表達「每個群組的第一個與最後一個值」：
視窗函式版本
WITH ranked AS (
SELECT
student_id,
subject,
score,
exam_date,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date ASC
) AS first_score,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date DESC
) AS latest_score,
COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count
FROM Scores
)
SELECT DISTINCT student_id, subject, first_score, latest_score
FROM ranked
WHERE exam_count >= 2 AND latest_score > first_score
ORDER BY student_id, subject;
Enter fullscreen mode Exit fullscreen mode
💡 核心概念
視窗函式通常只需要引擎對資料進行一次排序/分區，然後在單次遍歷中計算每個分區的所有值。相比之下，我的關聯子查詢版本會針對每個群組重新執行
MIN/MAX/查詢子查詢——在最壞情況下（或沒有良好索引時），這些內層查詢可能退化為針對每個群組重新掃描相關列，而不是單一統一的遍歷。
不過——對我原始解答的公平說明：由於有 GROUP BY，關聯子查詢只會針對每個相異的 (student_id, subject) 組合執行一次，而不是針對每一列原始資料執行。有了適當的複合索引 (student_id, subject, exam_date)，MySQL 幾乎可以立即滿足每個 MIN/MAX 查詢與分數的點查詢，因此在實際資料集大小下，這個查詢的表現相當不錯——它並非人們有時假設的關聯子查詢必然是 O(n²) 的最壞情況。視窗函式版本仍然是表達這種邏輯的更「現代 SQL」方式，而且擴展性更可預測，但一旦建立索引，兩者之間的差異通常比人們預期的要小。
經驗法則：
- 當你需要表達「針對每一列/群組，針對相關範圍提出針對性問題」時，請選擇關聯子查詢——它們在這種情況下極為易讀。
- 當你要在整個分區資料集中計算 first/last/rank/running-total 這類邏輯時，請選擇視窗函式——它們通常擴展性更好，且避免每個群組重複執行。
- 在假設哪一種是「最快的」之前，請務必在你的實際資料上檢查
EXPLAIN——資料大小、索引和 MySQL 版本都比教科書的複雜度故事更重要。
小結
關聯子查詢擅長易讀性與針對性的每群組查詢；視窗函式擅長規模，因為它們避免重複執行相同的計算。
10. 結論與後續練習
從這整篇文章中要帶走的核心想法可以用一句話概括：
關聯子查詢只是一個小型查詢，它一次從外層列取得一個值，並回答針對該值範圍的問題。
一旦這個概念被理解，大多數「困難」的關聯子查詢問題就不再困難——它們只是變成「我需要針對每一列/群組問什麼小問題，以及它需要外層查詢的什麼值？」
如果你想進一步鍛鍊這個技能，以下是一些直接依賴相同技能的題目：
- Second Highest Salary（經典的關聯子查詢熱身）
- Department Top Three Salaries
- Rank Scores
- Consecutive Numbers
- Employees Earning More Than Their Managers
請嘗試用我這裡展示的方式解決每一題兩次：一次用關聯子查詢（或視窗函式），一次用紙筆像迴圈一樣手動追蹤。迴圈追蹤才是真正建立直觀理解的方法——SQL 語法只是之後對這種直觀理解的編碼。
最後的想法
關聯子查詢通常被認為是 SQL 最棘手的概念之一——不是因為語法困難，而是因為很容易迷失哪個查詢正在執行，以及正在參照哪一列。
一旦你意識到內層查詢只是從外層查詢的目前列「借用」值，這個概念就會變得更加直觀。
希望這篇文章能讓這個心智模型更清楚一些。
祝學習愉快！🚀
0 Comments
Log in to join the conversation.No comments yet. Be the first to share your thoughts.