WITH RECURSIVE — Sinh chuỗi và đi qua phân cấp bằng truy vấn đệ quy

Dữ liệu dùng trong bài này — bảng employee

WITH RECURSIVE là cách viết CTE tự gọi chính nó để tạo ra từng hàng một.

Động tác đưa kết quả trước đó làm đầu vào tiếp theo và lặp lại này được gọi là đệ quy.

Tài liệu là bảng employee (30 hàng; cột manager_id trỏ tới emp_id của quản lý, một cấu trúc tự tham chiếu).

Trước khi bắt đầu các bài tập, hãy xem qua định nghĩa cột và dữ liệu mẫu của bảng employee, và cả SELECT tối giản mà chúng ta sẽ dùng làm neo của CTE đệ quy.

① Chạy SELECT 1; và xác nhận rằng nó trả về đúng giá trị 1 trong một hàng (đây là SELECT đơn giản nhất chúng ta sẽ dùng làm neo của CTE đệ quy).

② Chạy PRAGMA table_info(employee); để kiểm tra định nghĩa cột.

③ Chạy SELECT * FROM employee LIMIT 5; để xem trước 5 hàng đầu. Cũng hãy quan sát cách NULL xuất hiện ở cột manager_id (nhân viên không có quản lý).

3 phần tạo nên một truy vấn đệ quy — neo / phần đệ quy / UNION ALL

Dùng đoạn mã bên dưới, chúng ta sẽ đi qua 3 phần — ① neo (SELECT 1) / ② phần đệ quy (SELECT n + 1 FROM seq WHERE n < 5) / ③ `UNION ALL` — từng phần một.

-- Mã tham chiếu cho mục này. Chúng ta sẽ đi qua 3 phần ① / ② / ③ theo thứ tự
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① neo
  UNION ALL                           -- ③ UNION ALL (chất kết dính giữa neo và phần đệ quy)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② phần đệ quy
)
SELECT n FROM seq;

-- Kết quả (5 hàng): n=1, 2, 3, 4, 5

① Neo — phần SELECT 1

Neo là SELECT tạo ra hàng đầu tiên.

SELECT 1 trong mã tham chiếu là neo — nó chỉ là một SELECT bình thường, viết một lần, không tự tham chiếu.

Giá trị (1) trả về ở đây trở thành nội dung đầu tiên của seq(n), rồi được chuyển cho phần đệ quy làm giá trị khởi tạo của n.

② Phần đệ quy — phần SELECT n + 1 FROM seq WHERE n < 5

-- Mã tham chiếu (lặp lại). Mục này giải thích dòng ② phần đệ quy
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① neo
  UNION ALL                           -- ③ UNION ALL (chất kết dính)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② phần đệ quy ← phần đang giải thích ★
)
SELECT n FROM seq;

Trong WITH RECURSIVE seq(n), phần seq(n) là khai báo tên CTE (seq) và tên cột (n). Nội dung bắt đầu là `1` của neo, và từ đó về sau nó giữ kết quả của phần đệ quy.

SELECT n + 1 có nghĩa là "giá trị trước đó + 1" mỗi lần.

n khởi tạo là 1, nên n + 1 tiếp theo là 2, rồi 3, và cứ thế — quá trình này tiếp tục cho đến khi WHERE ngừng trả về hàng.

③ UNION ALL — phần `UNION ALL` của mã

-- Mã tham chiếu (lặp lại). Mục này giải thích dòng ③ UNION ALL
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- neo
  UNION ALL                           -- ★ phần đang giải thích ★ trông như một dòng, nhưng…
  SELECT n + 1 FROM seq WHERE n < 5   -- phần đệ quy
)
SELECT n FROM seq;

-- …bên trong DBMS, nó tương đương với "xếp chồng những gì phần đệ quy tạo ra, mỗi vòng lặp một UNION ALL":

SELECT 1            -- neo (chỉ chạy một lần)
UNION ALL           -- ← lần xếp thứ 1: chồng đầu ra của vòng 1
SELECT 2
UNION ALL           -- ← lần xếp thứ 2
SELECT 3
UNION ALL           -- ← lần xếp thứ 3
SELECT 4
UNION ALL           -- ← lần xếp thứ 4
SELECT 5;

-- Kết quả là [1, 2, 3, 4, 5] dù cách nào
-- Nói cách khác, "UNION ALL" duy nhất bên trong WITH RECURSIVE ≒ UNION ALL lặp lại cho từng vòng

UNION ALL là toán tử xếp chồng kết quả của hai hoặc nhiều SELECT theo chiều dọc.

Chúng ta dùng UNION ALL (giữ tất cả hàng) thay vì UNION (loại trùng lặp) vì chúng ta muốn mọi hàng sinh ra trong quá trình đệ quy đều có mặt trong kết quả cuối.

Ví dụ 1 — Sinh chuỗi (1 tới 5)

Yêu cầu: "Tôi muốn sinh chuỗi từ 1 tới 10 ngay lập tức dưới dạng các hàng, mà không cần bảng chuỗi có sẵn." Ngay cả khi không có bảng chuỗi gốc, bạn vẫn có thể dựng nó bằng CTE đệ quy. (Nếu bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Định nghĩa CTE cho chuỗi với WITH RECURSIVE. Neo nên là một SELECT trả về giá trị khởi tạo 1.

② Trong phần đệ quy, tham chiếu chính CTE và trả về giá trị trước đó cộng 1. Điều kiện dừng nên là "tiếp tục miễn là giá trị hiện tại nhỏ hơn 10".

③ Trong truy vấn chính, trả về chuỗi đã sinh theo thứ tự tăng dần.

Ví dụ 2 — Dự báo tăng lương (N năm nữa lương sẽ là bao nhiêu với mức 5%/năm?)

Ví dụ 1 là một chuỗi số trừu tượng, nhưng ở đây chúng ta sẽ xem một đệ quy thực tế bắt đầu từ dữ liệu thực của bảng `employee`.

Với lương hiện tại của Sam (emp_id=19), chúng ta sẽ dự báo lương sau 5 năm nếu nó tăng 5% mỗi năm.

Chỉ cần đổi phần đệ quy thành projected * 1.05 là bạn có một chuỗi tăng theo lãi kép.

-- Dự báo tăng lương: nếu lương của Sam (emp_id=19) tăng 5% mỗi năm, sau N năm là bao nhiêu?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- neo: lấy lương hiện tại từ employee
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- phần đệ quy: tăng 5% mỗi năm
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- Kết quả (6 hàng): dự báo 5 năm bắt đầu từ lương hiện tại 4100000 của Sam
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← neo (employee.salary nguyên trạng)
--    1  | 4305000   ← phần đệ quy (4100000 × 1.05)
--    2  | 4520250   ← phần đệ quy (4305000 × 1.05)
--    3  | 4746263   ← phần đệ quy (4520250 × 1.05, áp ROUND)
--    4  | 4983576   ← phần đệ quy (4746262.5 × 1.05, áp ROUND)
--    5  | 5232754   ← phần đệ quy. Tiếp theo year=5 làm WHERE year<5 sai → dừng

Có hai điểm khác so với ví dụ chuỗi.

① Neo lấy một giá trị từ bảng employee (SELECT 0, salary FROM employee WHERE emp_id = 19) — bạn có thể bắt đầu từ dữ liệu thực.

② Đó là CTE 2 cột (year và projected), và phần đệ quy cập nhật cả hai cột cùng lúc bằng year + 1 và projected * 1.05.

Một khi bạn có thể lấy một giá trị từ bảng thực trong neo, bạn có thể viết các mô phỏng bắt đầu từ giá trị đó (lãi kép, dự báo dân số, tính số bước tới mục tiêu, v.v.).

Yêu cầu: "Bắt đầu từ lương hiện tại của Bob (emp_id=2), tôi muốn dự báo sau 5 năm nếu nó tăng 10% mỗi năm." (Nếu bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Định nghĩa tên CTE là growth và các cột là year và projected trong WITH RECURSIVE.

② Neo nên lấy salary từ employee cho emp_id = 2, với year bắt đầu từ 0.

③ Phần đệ quy nên trả về year + 1 và projected * 1.10, với điều kiện dừng year < 5.

④ Trong truy vấn chính, trả về year và ROUND(projected) AS projected_salary theo thứ tự tăng dần.

Ví dụ 3 — Dùng với JOIN (đi qua chuỗi manager_id từng bước một)

Tiếp theo chúng ta sẽ xem ứng dụng kinh điển khác của CTE đệ quy, đi qua phân cấp tự tham chiếu.

employee.manager_id giữ "emp_id của quản lý của bạn", nên bằng cách đi theo nó từng bước, bạn có thể đi lên qua sơ đồ tổ chức: nhân viên → quản lý → quản lý của quản lý, và cứ thế.

Trong ví dụ chuỗi, chúng ta dùng n+1 để tạo giá trị tiếp theo; ở đây chúng ta lấy hàng tiếp theo (quản lý) bằng JOIN để tiến chuỗi.

-- Đi qua phân cấp: từ nhân viên khởi đầu (emp_id=19, Sam) lên qua các quản lý
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① neo: hàng khởi đầu
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② phần đệ quy: lấy hàng được nối
)
SELECT emp_id, name, manager_id FROM chain;

-- Kết quả (2 hàng):
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← neo (điểm khởi đầu)
--     2   | Bob  |  NULL       ← hàng do phần đệ quy tạo; manager_id là NULL nên vòng tiếp theo dừng

① Neo (SELECT ... FROM employee WHERE emp_id = 19) lấy hàng khởi đầu.

Ở đây nó đưa hàng của emp_id=19 (Sam) vào chain làm nội dung đầu tiên.

Lưu ý rằng WITH RECURSIVE chain AS (...) không có khai báo tên cột như chain(...). Đó là vì SELECT của neo đã cho chain tên cột tự nhiên (emp_id / name / manager_id) từ các cột của employee. Khi đã dễ đọc, bạn có thể bỏ qua.

② Phần đệ quy (JOIN chain c ON e.emp_id = c.manager_id) nối employee với chain trước đó và lấy hàng được nối vào chain.

Một khi manager_id của hàng được nối trở thành NULL, vòng tiếp theo không tìm được đối tác để nối, kết quả là 0 hàng, và đệ quy dừng tự nhiên.

(Không cần điều kiện dừng tường minh như WHERE n < 5 của ví dụ chuỗi — điểm kết thúc của phân cấp chính là điều kiện dừng.)

Yêu cầu: "Bắt đầu từ emp_id = 19 (Sam), tôi muốn đi qua chuỗi manager_id lên trên — quản lý của Sam, rồi quản lý của người đó, và cứ thế — để liệt kê chuỗi cấp bậc."

① Định nghĩa CTE cho phân cấp với WITH RECURSIVE. Neo nên lấy một hàng (emp_id, name, manager_id) từ employee cho emp_id = 19.

② Trong phần đệ quy, nối employee với CTE trên "employee.emp_id = manager_id của CTE" và lấy hàng của quản lý (emp_id, name, manager_id) từng bước một.

③ Trong truy vấn chính, trả về emp_id, name, manager_id.

Bài tập 3 đi từ dưới lên trên qua các quản lý.

Bây giờ chúng ta sẽ đi theo hướng ngược lại: "Bắt đầu từ emp_id = 1 (Alice), tôi muốn đi qua chuỗi manager_id xuống qua các cấp dưới của Alice, rồi cấp dưới của họ, và cứ thế — để liệt kê tất cả mọi người trong đội của Alice."

① Định nghĩa CTE cho cấp dưới với WITH RECURSIVE. Neo nên lấy một hàng (emp_id, name, manager_id) từ employee cho emp_id = 1.

② Trong phần đệ quy, nối employee với CTE trên "employee.manager_id = emp_id của CTE" và lấy các cấp dưới của hàng hiện tại (emp_id, name, manager_id) từng bước một.

③ Trong truy vấn chính, trả về emp_id, name, manager_id theo thứ tự emp_id tăng dần.

Ví dụ 4 — Đệ quy tạo ra nhiều hàng cùng lúc (toàn bộ cây tổ chức)

Ví dụ 3 đi từng hàng một (Sam → Bob → dừng).

Ở đây chúng ta sẽ xem trường hợp neo hoặc phần đệ quy tạo ra nhiều hàng cùng lúc.

Kịch bản: "Bắt đầu từ 5 cấp điều hành (nhân viên không có quản lý) và lấy toàn bộ cây tổ chức 30 người cùng lúc." Bạn sẽ thấy truy vấn đệ quy đi qua nhiều nhánh song song theo chiều rộng.

-- Bắt đầu từ tất cả cấp điều hành (nhân viên không có quản lý) và đệ quy lấy tất cả mọi người trong tổ chức
WITH RECURSIVE org_tree AS (
  -- ① neo: 5 nhân viên có manager_id = NULL (trả về nhiều hàng cùng lúc)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② phần đệ quy: lấy các cấp dưới trực tiếp của các hàng trước đó (cũng nhiều hàng)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- Kết quả (30 hàng = toàn công ty):
--   Neo (5 hàng): emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   Phần đệ quy vòng 1 (25 hàng): 9 cấp dưới của Alice + 8 cấp dưới của Bob + 8 cấp dưới của Carol
--   Phần đệ quy vòng 2 (0 hàng): các cấp dưới không có cấp dưới riêng → dừng
--   Tổng: 5 + 25 = 30 hàng

Có hai điểm khác so với Ví dụ 3.

① Neo trả về nhiều hàng (5) cùng lúc — mọi hàng khớp WHERE manager_id IS NULL đều được dùng để khởi tạo CTE.

② Phần đệ quy đi qua nhiều nhánh song song — với 5 người trong org_tree, một vòng của phần đệ quy lấy cấp dưới của mỗi người cùng lúc trong cùng một vòng lặp (9 của Alice + 8 của Bob + 8 của Carol = 25 hàng).

Nói cách khác, truy vấn đệ quy có thể làm hơn "đi sâu từng hàng một" — chúng cũng có thể mở rộng nhiều nhánh song song theo chiều rộng.

Yêu cầu: "Bắt đầu từ Bob (emp_id=2) và Carol (emp_id=3) cùng lúc, tôi muốn lấy cấp dưới của họ trong một truy vấn." (Nếu bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Tên CTE nên là team trong WITH RECURSIVE; bỏ qua khai báo tên cột (dùng tên cột tự nhiên của employee).

② Neo nên lấy hai hàng từ employee nơi emp_id là 2 hoặc 3.

③ Phần đệ quy nên nối employee với CTE trên "employee.manager_id = emp_id của CTE" và lấy các cấp dưới.

④ Trong truy vấn chính, trả về emp_id, name, manager_id theo thứ tự emp_id tăng dần.

Mẹo — Vì sao dùng WITH RECURSIVE?

Ba tình huống mà WITH RECURSIVE tỏa sáng

Truy vấn đệ quy dành cho những tình huống mà SQL thuần hoặc không làm được hoặc làm rất vất vả. Các trường hợp sử dụng chính là ba:

① Đi qua phân cấp — sơ đồ tổ chức, thư mục, bảng vật tư — các cấu trúc tự tham chiếu mà bạn không biết sâu bao nhiêu. Thay vì xếp chồng vài JOIN bằng tay, bạn có thể viết ở dạng dừng tự nhiên ở cuối dữ liệu.

② Sinh nhiều hàng — chuỗi, ngày liên tiếp, lịch — thay vì viết tay UNION ALL cả trăm lần, một dòng với WHERE làm được.

③ Đẩy vòng lặp phía ứng dụng vào SQL — viết vòng lặp trong ứng dụng để đi qua ID cha gặp vấn đề N+1 truy vấn, nhưng truy vấn đệ quy xử lý trong một truy vấn duy nhất.

Nếu JOIN hoặc GROUP BY thuần làm được, bạn không cần đệ quy. Nhưng khi bạn ở một trong ba tình huống trên, đó là công cụ đầu tiên cần với tới.