Cách hoạt động và ứng dụng của recursive CTE

Giới thiệu về Recursive CTE – Cách hoạt động và ứng dụng trong phân tích dữ liệu

Ngày đăng: 23/01/2026

Định nghĩa Recursive CTEs (CTEs đệ quy)

Recursive Common Table Expressions (Recursive CTEs) là một kỹ thuật mạnh mẽ trong SQL, cho phép thực hiện các truy vấn lặp đi lặp lại trên một tập dữ liệu bằng cách tham chiếu chính nó. Recursive CTEs đặc biệt hữu ích trong các bài toán như phân tích cấu trúc phân cấp, tìm đường đi trong đồ thị hoặc tính toán lặp.

Ví dụ, với một EmployeeKey cụ thể, Recursive CTEs cho phép truy vấn toàn bộ các cấp bậc liên quan (từ nhân viên hiện tại đến các cấp quản lý cao hơn) mà không cần biết trước số cấp, đồng thời hiển thị kết quả theo từng mức độ (level) rõ ràng:

EmployeeKey

EmployeeName

ManagerKey

1

CEO

NULL

2

Director

1

3

Manager

2

4

Team Lead

3

5

Staff A

4

6

Staff B

4

Giả định bài toán:
Truy vấn tất cả các cấp quản lý phía trên của nhân viên có EmployeeKey = 5.

Level

EmployeeKey

EmployeeName

ManagerKey

0

5

Staff A

4

1

4

Team Lead

3

2

3

Manager

2

3

2

Director

1

4

1

CEO

NULL

Recursive CTEs được chia thành hai thành phần chính:

  • Anchor Member (Thành viên neo): Thành viên neo cung cấp điểm bắt đầu cho truy vấn, đóng vai trò là tập kết quả cơ bản để xây dựng các bước đệ quy tiếp theo.
  • Recursive Member (Thành viên đệ quy): Thành viên này được liên kết với Anchor Member thông qua toán tử UNION ALL, cho phép truy vấn lặp lại dựa trên tập kết quả trước đó, tạo ra hành vi đệ quy.

CTEs đệ quy hoạt động như một vòng lặp, trong đó mỗi bước mới phụ thuộc vào kết quả của bước trước đó. Quá trình này tiếp tục cho đến khi đạt điều kiện dừng, hoặc đạt đến giới hạn tối đa được định nghĩa trong cơ sở dữ liệu.

Cấu trúc câu lệnh

Cấu trúc cơ bản của Recursive CTEs được viết như sau:

WITH cte_name AS (
  -- Anchor member
  initial_query
  UNION ALL
  -- Recursive member
  recursive_query
)
SELECT *
FROM cte_name;

Cách viết câu lệnh:

  • Bắt đầu với thành viên neo (Anchor Member): Đây là truy vấn cơ bản để xác định điểm bắt đầu.
  • Thực hiện hàm đệ quy trong Recursive Member: Thành phần này gọi lại CTE để xây dựng kết quả dựa trên bước trước đó.
  • Xác định điều kiện dừng (nếu cần): Đảm bảo vòng lặp không chạy vô hạn bằng cách định nghĩa điều kiện dừng trong truy vấn hoặc thiết lập giới hạn.

Một số lưu ý:

  • Số lượng cột và kiểu dữ liệu: Thành viên neo và thành viên đệ quy phải trả về cùng số lượng cột và cùng định dạng dữ liệu.
  • Điều kiện dừng: Đảm bảo truy vấn đệ quy không chạy vô hạn.

Recursive CTEs hoạt động như sau:

  • Bước khởi tạo: Chạy thành viên neo để tạo ra tập dữ liệu ban đầu.
  • Bước đệ quy: Chạy thành viên đệ quy, sử dụng kết quả từ bước trước để tạo ra dữ liệu mới.
  • Lặp lại: Tiếp tục thực hiện bước đệ quy cho đến khi đạt điều kiện dừng.

Quá trình này giúp giải quyết hiệu quả các bài toán phân cấp và tính toán đệ quy.

Một số ứng dụng

Tìm các thành phần phụ thuộc trong cơ cấu tổ chức

Để lấy ví dụ, chúng ta sẽ tạo một bảng employees chứa thông tin về nhân viên và cấp trên của họ:

CREATE TABLE #employees (
  id INT,
  first_name NVARCHAR(50),
  last_name NVARCHAR(50),
  boss_id INT
);

INSERT INTO #employees VALUES
  (1, 'Domenic', 'Leaver', 5),
  (2, 'Cleveland', 'Hewins', 1),
  (3, 'Kakalina', 'Atherton', 8),
  (4, 'Roxanna', 'Fairlie', NULL),
  (5, 'Hermie', 'Comsty', 4),
  (6, 'Pooh', 'Goss', 8),
  (7, 'Faulkner', 'Challiss', 5),
  (8, 'Bobbe', 'Blakeway', 4),
  (9, 'Laurene', 'Burchill', 1),
  (10, 'Augusta', 'Gosdin', 8);
cấu trúc dữ liệu trong bài toán ví dụ CTE

Câu truy vấn Recursive CTE dưới đây liệt kê tất cả nhân viên và cấp trên của họ:

WITH org_chart AS (
  SELECT
    id,
    first_name,
    last_name,
    boss_id,
    0 AS hierarchy_level
  FROM #employees
  WHERE boss_id IS NULL
  UNION ALL
  SELECT
    e.id,
    e.first_name,
    e.last_name,
    e.boss_id,
    o.hierarchy_level + 1
  FROM #employees e
  JOIN org_chart o ON e.boss_id = o.id
)
SELECT
  o.first_name AS employee_first_name,
  o.last_name AS employee_last_name,
  e.first_name AS boss_first_name,
  e.last_name AS boss_last_name,
  o.hierarchy_level
FROM org_chart o
LEFT JOIN #employees e ON o.boss_id = e.id
ORDER BY o.hierarchy_level, o.boss_id;

Trong câu lệnh này, thành viên neo sẽ lấy danh sách các nhân viên ở cấp cao nhất (không có cấp trên) bằng cách:

  • Lọc ra các nhân viên có boss_id là NULL trong bảng #employees.
  • Gán giá trị hierarchy_level = 0 để đánh dấu rằng đây là cấp cao nhất trong tổ chức.

Thành viên đệ quy sẽ tìm các nhân viên thuộc cấp dưới của những nhân viên đã được tìm thấy ở bước trước đó bằng cách:

  • Thực hiện phép nối JOIN giữa bảng #employees (tất cả nhân viên) và kết quả của org_chart (CTE đệ quy).
  • Điều kiện ON e.boss_id = o.id đảm bảo rằng chỉ các nhân viên có boss_id trùng với id của một nhân viên từ tập kết quả trước đó được thêm vào kết quả mới.
  • Giá trị hierarchy_level được tăng thêm 1, để biểu thị cấp dưới trực tiếp.
  • Thành viên đệ quy sẽ tiếp tục chạy cho đến khi không còn nhân viên nào có boss_id trùng với id trong kết quả trước đó (hoặc đạt giới hạn đệ quy của SQL Server).

Ở bước SELECT cuối cùng, chúng ta hiển thị danh sách các nhân viên cùng tên cấp trên của họ bằng cách thực hiện phép join org_chart với bảng #employees.

Chúng ta được kết quả cuối cùng như sau:

bảng dữ liệu trước CTE

Tìm độ dài các tuyến đường giữa các thành phố

Ví dụ: Dữ liệu khoảng cách giữa các thành phố tại Việt Nam:

-- Tạo bảng tạm chứa các tuyến đường
CREATE TABLE #city_routes (
  city_from NVARCHAR(50),
  city_to NVARCHAR(50),
  distance DECIMAL(10, 1)
);

-- Thêm dữ liệu với tên thành phố
INSERT INTO #city_routes VALUES
  ('Ha Noi', 'Hai Phong', 102.5),
  ('Hai Phong', 'Ha Long', 71.2),
  ('Ha Long', 'Ha Noi', 155.3),
  ('Ha Noi', 'Nam Dinh', 65.0),
  ('Nam Dinh', 'Hai Duong', 70.5),
  ('Hai Duong', 'Hai Phong', 60.0),
  ('Ha Noi', 'Thai Nguyen', 65.7),
  ('Thai Nguyen', 'Vinh Phuc', 40.8),
  ('Vinh Phuc', 'Ha Noi', 35.0),
  ('Ha Noi', 'Bac Giang', 50.5),
  ('Bac Giang', 'Ha Long', 100.3),
  ('Hai Phong', 'Quang Ninh', 105.7),
  ('Quang Ninh', 'Ha Long', 50.2),
  ('Ha Long', 'Ha Noi', 155.3);

Truy vấn đệ quy sau sẽ tìm các tuyến đường từ Hà Nội đến Hạ Long và độ dài mỗi tuyến:

WITH Routes AS (
  SELECT
    city_from,
    city_to,
    CAST(city_from + '->' + city_to AS VARCHAR(MAX)) AS route,
    distance
  FROM #city_routes
  WHERE city_from = 'Ha Noi'
  UNION ALL
  SELECT
    r.city_from,
    c.city_to,
    CAST(r.route + '->' + c.city_to AS VARCHAR(MAX)),
    r.distance + c.distance
  FROM Routes r
  INNER JOIN #city_routes c ON r.city_to = c.city_from
  WHERE r.route NOT LIKE '%' + c.city_to + '%'
)
SELECT *
FROM Routes
WHERE city_to = 'Ha Long';

Trong câu lệnh này, thành viên neo lấy ra thông tin các tuyến đường có điểm bắt đầu là Ha Noi.

Trong phần đệ quy:

  • Thành viên đệ quy sử dụng INNER JOIN #city_routes c ON r.city_to = c.city_from để lấy thành phố đích (city_to) từ lần trước làm điểm xuất phát (city_from) cho lần tiếp theo.
  • Điều kiện r.route NOT LIKE ‘%’ + c.city_to + ‘%’ đảm bảo không quay lại các thành phố đã đi qua, tránh tạo ra vòng lặp vô hạn.
  • Sau khi phần neo trả về kết quả ban đầu, phần đệ quy tiếp tục mở rộng các tuyến đường, cộng dồn khoảng cách, và lặp lại cho đến khi không còn tuyến đường nào mới.

Cuối cùng, ta lọc ra các tuyến đường có điểm kết thúc là Ha Long từ tất cả các tuyến đường đã được tính toán qua các vòng lặp đệ quy.

kết quả cuối của CTE

So sánh Recursive CTEs và Non-recursive CTEs

CTE đệ quy và CTE thông thường có các điểm khác biệt chính như sau:

Tiêu chí

Recursive CTE

Non-recursive CTE

Cấu trúc

Gồm 2 phần: phần anchor (khởi tạo) và phần đệ quy (recursive).

Chỉ có một phần duy nhất là truy vấn không đệ quy.

Ứng dụng

Thường được dùng cho các truy vấn có tính chất đệ quy như tìm đường đi, cây phân cấp, hay tổ chức phân cấp.

Thường dùng cho các truy vấn đơn giản mà không cần lặp lại hoặc đệ quy.

Tính phức tạp

Phức tạp hơn, vì phải đảm bảo điều kiện kết thúc để tránh lặp vô hạn, và có thể xử lý dữ liệu động.

Đơn giản hơn, chỉ là truy vấn thông thường không cần các vòng lặp.

Sử dụng bộ nhớ

Cần nhiều bộ nhớ và tài nguyên, vì phải lưu trữ các trạng thái qua mỗi vòng lặp.

Tiêu tốn ít bộ nhớ hơn, vì chỉ lưu trữ kết quả của một truy vấn đơn.

Khả năng tái sử dụng

Không thể tái sử dụng trực tiếp các kết quả của mỗi vòng lặp trong lần truy vấn tiếp theo.

Có thể tái sử dụng kết quả từ CTE này trong các truy vấn khác.

 

Kết luận

Bài viết này đã giới thiệu Recursive CTEs cũng như giải thích cách hoạt động và đưa ra một vài ứng dụng trong phân tích dữ liệu. Hy vọng bài viết đã cung cấp thêm cho các bạn kiến thức hữu ích để giải quyết các bài toán phức tạp trong thực tế. Để tìm hiểu sâu hơn, các bạn có thể mở rộng kiến thức sang các chủ đề liên quan như truy vấn dữ liệu phân cấp nâng cao, tối ưu hiệu năng Recursive CTEs, xử lý vòng lặp (cycle detection), cũng như so sánh Recursive CTEs với các kỹ thuật khác như window functions. Ngoài ra, việc kết hợp Recursive CTEs với các bài toán thực tế trong phân tích tổ chức, mạng lưới quan hệ hay chuỗi phụ thuộc dữ liệu sẽ giúp nâng cao khả năng ứng dụng trong phân tích dữ liệu.

Tài liệu tham khảo:

Microsoft. (n.d.). Recursive queries using common table expressions (Transact-SQL).
Truy xuất từ https://learn.microsoft.com/en-us/sql/t-sql/queries/recursive-common-table-expression-transact-sql?view=sql-server-ver17

Microsoft. (n.d.). Recursive CTEs guideline (SQL Server).
Truy xuất từ https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver17#guidelines-for-recursive-common-table-expressions

Chia sẻ bài viết này

Bài viết liên quan

Để lại một bình luận

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

This site uses Akismet to reduce spam. Learn how your comment data is processed.