Tài liệu SQLite chạy trực tiếp: CTE đệ quy

Q: CTE đệ quy trong SQLite là gì?

CTE đệ quy là một truy vấn WITH RECURSIVE dựng tập kết quả bằng cách lặp đi lặp lại việc tham chiếu chính nó. Nó có hai phần nối bằng UNION ALL: truy vấn anchor tạo ra các hàng khởi đầu, và truy vấn đệ quy tạo thêm hàng từ bước trước. SQLite tiếp tục chạy phần đệ quy cho đến khi nó không trả về hàng mới nào.

Q: Khi nào nên dùng WITH RECURSIVE trong SQLite?

Hãy dùng nó khi bạn cần duyệt một cây hoặc đồ thị (nhân viên và quản lý, danh mục và danh mục con, bình luận dạng luồng) hoặc sinh một dãy giá trị (mọi ngày trong một khoảng, các số từ 1 đến 100). Join thông thường xử lý được một hoặc hai cấp; CTE đệ quy xử lý độ sâu bất kỳ mà không cần biết trước.

Q: Làm sao tránh vòng lặp vô hạn trong CTE đệ quy của SQLite?

Đảm bảo truy vấn đệ quy có điều kiện dừng: một mệnh đề WHERE cuối cùng sẽ trả về 0 hàng, hoặc một bộ đếm có giới hạn. Với đồ thị có chu trình, hãy lưu đường đi đã duyệt trong một cột và loại các hàng đã có trong đó. Để phòng hờ, thêm LIMIT vào truy vấn ngoài để đệ quy mất kiểm soát không thể làm đầy bộ nhớ.

CTE đệ quy trong SQLite: WITH RECURSIVE cho cây và dãy số

Cách CTE đệ quy hoạt động trong SQLite: cấu trúc anchor và phần đệ quy, duyệt cây cha con, sinh dãy giá trị và tránh vòng lặp vô hạn.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

Bởi Kevin Spektor, Đồng sáng lập và CTO

Đăng ngày May 3, 2026

Đệ quy trong SQL nghe lạ tai, cho đến khi bạn thấy nó

Hầu hết truy vấn trả về các hàng từ dữ liệu đã có sẵn. Một CTE đệ quy thì khác: nó dựng các hàng bằng cách đưa chính kết quả đầu ra của mình trở lại làm đầu vào, từng bước một, cho đến khi không còn gì mới để thêm. Đó là cách bạn duyệt một cây không biết trước độ sâu, hoặc sinh các số từ 1 đến 100 mà không cần bảng số.

Cấu trúc lúc nào cũng giống nhau:

WITH RECURSIVE name(columns) AS (
    -- anchor: the starting rows
    SELECT ...
    UNION ALL
    -- recursive: rows derived from the previous step
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Anchor ở trên, UNION ALL, truy vấn đệ quy ở dưới. SQLite chạy anchor một lần, rồi liên tục chạy phần đệ quy, mỗi lần dùng các hàng được tạo ở vòng trước, cho đến khi nó không trả về hàng mới nào. Khi đó nó dừng.

Đếm từ 1 đến 10

CTE đệ quy đơn giản nhất sinh ra một dãy số. Không cần bảng nào:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

Lần theo từng bước:

Anchor tạo một hàng: n = 1.
Bước đệ quy lấy hàng đó, tính n + 1 = 2, và 2 < 10 đúng, nên nó giữ hàng này.
Lần lặp tiếp theo lấy n = 2 và tạo ra n = 3. Cứ thế tiếp tục.
Khi n đạt 10, 10 < 10 sai, bước đệ quy không trả về hàng nào, và SQLite dừng.

WHERE n < 10 là điều kiện dừng. Không có nó, truy vấn chạy mãi mãi.

Sinh dãy ngày

Cùng ý tưởng, hữu ích trong báo cáo thực tế: điền đủ mọi ngày trong một khoảng, kể cả những ngày không có gì xảy ra:

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

Thông thường bạn sẽ LEFT JOIN dãy này với một bảng sự kiện để đếm đúng những ngày không có sự kiện nào. Một câu GROUP BY date thông thường bỏ qua hoàn toàn các ngày trống; dãy ngày cho bạn một hàng cho mỗi ngày, bất kể thế nào.

Duyệt cây cha con

Trường hợp dùng kinh điển. Đây là một bảng nhân viên, trong đó mỗi hàng trỏ tới quản lý của mình:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

Anchor chọn gốc (người không có quản lý). Bước đệ quy join bảng nhân viên với CTE, tìm tất cả những ai có manager_id khớp với một id đã có trong CTE. Mỗi lần lặp đi sâu thêm một cấp. depth chỉ là một bộ đếm ta thêm vào để thụt lề kết quả.

Cách này dùng được cho cây ở mọi độ sâu. Hai cấp hay mười cấp, truy vấn không thay đổi.

Tìm tất cả tổ tiên của một hàng cụ thể

Đảo chiều lại. Thay vì đi xuống từ gốc, hãy đi lên từ một nhân viên cụ thể để tìm toàn bộ chuỗi quản lý của người đó:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

Anchor là nhân viên xuất phát. Mỗi bước đệ quy nhảy lên cấp cha. SQLite dừng khi chạm tới gốc: manager_id IS NULL, nên phép join không tìm thấy gì.

Mẫu này hữu ích cho breadcrumb, bình luận dạng luồng, đường dẫn danh mục, và bất cứ chỗ nào bạn cần "đi lên tới đỉnh".

Điều kiện dừng và vòng lặp vô hạn

Bug hay gặp nhất là quên điều kiện dừng hoặc viết một điều kiện không bao giờ kích hoạt. So sánh:

-- Runs forever:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

Không có mệnh đề WHERE nào để có lúc trả về 0 hàng. SQLite sẽ vui vẻ cố đếm tới vô cùng.

Hai thói quen phòng thủ:

Luôn có một mệnh đề WHERE trong phần đệ quy để giới hạn sự tăng trưởng.
Thêm LIMIT vào câu SELECT bên ngoài như một lưới an toàn khi đang phát triển: nếu bạn viết sai điều kiện dừng, truy vấn vẫn kết thúc.

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

Bản thân CTE không có giới hạn, nhưng LIMIT 5 dừng truy vấn ngoài sớm. SQLite đủ thông minh để không tiếp tục đệ quy quá mức mà LIMIT cần. Hữu ích khi khám phá; không thay thế được điều kiện dừng thật sự trong code production.

Chu trình trong đồ thị

Cây không thể có chu trình. Đồ thị tổng quát thì có thể, và một CTE đệ quy ngây thơ sẽ lặp mãi nếu dữ liệu có chu trình. Cách sửa là lưu lại đường đi đã duyệt và từ chối ghé lại các nút cũ:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path là một chuỗi các nút đã ghé, phân cách bằng dấu phẩy. Trước khi thêm một nút mới, mệnh đề WHERE kiểm tra nút đó chưa có trong chuỗi. Không có lớp bảo vệ đó, chu trình 1 → 2 → 3 → 1 sẽ lặp mãi mãi.

SQL không có sẵn "tập đã ghé"; bạn tự dựng nó, thường dưới dạng một chuỗi hoặc bằng cách join với phần CTE đã có.

CTE đệ quy và self join

Nếu bạn chỉ cần một hoặc hai cấp, self join đơn giản và nhanh hơn:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Câu đó trả lời được "quản lý trực tiếp của mỗi người là ai". Nhưng nếu bạn cần "tất cả những ai báo cáo lên tới Ada, bất kể sâu đến đâu", tức độ sâu không biết trước, thì chỉ CTE đệ quy xử lý gọn gàng. Hãy chọn công cụ theo độ sâu bạn cần:

Độ sâu cố định và nhỏ: self join, có thể hai hoặc ba lần.
Độ sâu không biết trước hoặc tùy ý: WITH RECURSIVE.

Cách hình dung

CTE đệ quy là một vòng lặp được viết theo kiểu khai báo:

Anchor là giá trị khởi đầu của vòng lặp.
Truy vấn đệ quy là thân vòng lặp: nó tạo ra lô hàng tiếp theo từ các hàng hiện tại.
Điều kiện dừng là phép kiểm tra thoát vòng lặp: khi nó trả về 0 hàng, vòng lặp kết thúc.
UNION ALL gom mọi thứ vào tập kết quả cuối cùng.

Một khi hình dung được như vậy, cú pháp sẽ không còn thấy kỳ lạ. Bạn đang viết một vòng lặp for bằng SQL.

Tiếp theo: chỉ mục

CTE đệ quy duyệt rất nhiều hàng, và phép join bên trong bước đệ quy chạy ở mọi lần lặp. Nếu cột join không được đánh chỉ mục, hiệu năng sẽ tụt dốc rất nhanh. Chỉ mục là chương tiếp theo, và manager_id chính là loại cột được lợi từ chỉ mục.

Câu hỏi thường gặp

CTE đệ quy trong SQLite là gì?

CTE đệ quy là một truy vấn WITH RECURSIVE dựng tập kết quả bằng cách lặp đi lặp lại việc tham chiếu chính nó. Nó có hai phần nối bằng UNION ALL: truy vấn anchor tạo ra các hàng khởi đầu, và truy vấn đệ quy tạo thêm hàng từ bước trước. SQLite tiếp tục chạy phần đệ quy cho đến khi nó không trả về hàng mới nào.

Khi nào nên dùng WITH RECURSIVE trong SQLite?