CTE đệ quy: đi hết cây tổ chức mà không cần biết nó sâu bao nhiêu

Sơ đồ tổ chức có thể sâu 3 cấp hoặc 12. Bạn không thể viết sẵn 12 câu JOIN. Nhưng có cách viết một câu duy nhất.

Bài toán

Bảng nhân viên có quan_ly_id trỏ về chính bảng đó. Cần liệt kê toàn bộ cấp dưới của một người, ở mọi tầng.

Self-join chỉ đi được một tầng mỗi lần. Ba tầng cần ba JOIN. Mà bạn không biết trước cây sâu bao nhiêu.

Cấu trúc cố định của CTE đệ quy

WITH RECURSIVE cap_duoi AS (
  -- 1. Điểm neo: nơi bắt đầu
  SELECT id, ten, quan_ly_id, 1 AS cap
  FROM nhan_vien
  WHERE quan_ly_id IS NULL

  UNION ALL

  -- 2. Phần đệ quy: tự gọi lại chính nó
  SELECT n.id, n.ten, n.quan_ly_id, c.cap + 1
  FROM nhan_vien n
  JOIN cap_duoi c ON n.quan_ly_id = c.id
)
SELECT * FROM cap_duoi ORDER BY cap, ten;

Ba phần luôn giống nhau: điểm neo → UNION ALL → phần tự tham chiếu. Máy chạy phần hai lặp đi lặp lại cho đến khi không còn dòng mới.

Lỗi thường gặp

1. Dùng UNION thay vì UNION ALL

UNION khử trùng lặp ở mỗi vòng lặp, chậm hơn nhiều và có thể làm mất dòng hợp lệ.

2. Vòng lặp vô hạn

Nếu dữ liệu có vòng — A quản lý B, B quản lý A — truy vấn sẽ chạy mãi. Chặn bằng giới hạn độ sâu:

  WHERE c.cap < 20

Hoặc trên PostgreSQL, lưu đường đi đã qua bằng mảng và loại dòng đã ghé.

3. Quên từ khoá RECURSIVE

PostgreSQL, MySQL 8 và SQLite bắt buộc viết WITH RECURSIVE. SQL Server thì không cần — chỉ WITH.

Ứng dụng thứ hai: sinh dải ngày liên tục

WITH RECURSIVE cac_ngay AS (
  SELECT DATE '2026-01-01' AS ngay
  UNION ALL
  SELECT ngay + 1 FROM cac_ngay WHERE ngay < DATE '2026-01-31'
)
SELECT n.ngay, COALESCE(SUM(d.tong_tien), 0) AS doanh_thu
FROM cac_ngay n
LEFT JOIN don_hang d ON d.ngay_dat::date = n.ngay
GROUP BY n.ngay ORDER BY n.ngay;

Đây là cách duy nhất tạo bảng lịch trên hệ không có generate_series. Nó giải quyết dứt điểm vấn đề “tháng trống biến mất khỏi biểu đồ”.

Ứng dụng thứ ba: tìm số bị thiếu

LeetCode 1613 — sinh dãy id liên tục rồi LEFT JOIN để tìm những id chưa tồn tại. Cùng một kỹ thuật, đổi tên cột.

Luyện tập

• LeetCode 1613 — Find the Missing IDs
• LeetCode 1270 — All People Report to the Given Manager
• LeetCode 1729 — Find Followers Count
• HackerRank — Draw The Triangle 1 và 2
• HackerRank — Print Prime Numbers