Recursive CTE trong SQL: Xử lý cấu trúc dữ liệu phân cấp đa tầng (cây danh mục, sơ đồ nhân sự)

Khám phá kỹ thuật Recursive CTE (đệ quy) trong SQL: cách duyệt qua cây danh mục hàng hóa nhiều cấp, sơ đồ báo cáo cấp bậc nhân sự và hóa đơn nguyên vật liệu (BOM).

1. Thách thức khi xử lý dữ liệu phân cấp

Trong thực tế, dữ liệu thường tồn tại dưới dạng phân cấp cha – con nhiều tầng. Các bài toán phổ biến gồm có:

• Sơ đồ tổ chức: Nhân viên -> Quản lý trực tiếp -> Giám đốc -> CEO.

• Cây danh mục hàng hóa: Thời trang -> Thời trang nam -> Áo sơ mi -> Áo sơ mi trắng.

• Định mức nguyên vật liệu (BOM): Sản phẩm hoàn chỉnh được cấu thành từ nhiều linh kiện nhỏ.

Nếu chỉ dùng các câu lệnh JOIN thông thường, bạn sẽ không thể biết trước cần JOIN bao nhiêu lần khi số cấp bậc thay đổi. Đó là lý do bạn cần đến Recursive CTE (CTE đệ quy).

2. Recursive CTE là gì?

Recursive CTE là một CTE có khả năng tự gọi lại chính nó trong quá trình thực thi. Kỹ thuật này giúp vòng lặp chạy liên tục để duyệt qua từng tầng dữ liệu phân cấp cho đến khi hết dữ liệu.

Cấu trúc cơ bản của một Recursive CTE bao gồm 3 phần chính:

1. Anchor Member (Thành phần neo): Câu lệnh SELECT đầu tiên để lấy ra tầng gốc (ví dụ: Cấp cao nhất / Root).

2. UNION ALL: Nối kết quả của tầng trước với tầng tiếp theo.

3. Recursive Member (Thành phần đệ quy): Câu lệnh SELECT gọi lại chính CTE và tự JOIN với bảng ban đầu để lấy cấp con.

3. Cú pháp cơ bản của Recursive CTE

WITH RECURSIVE Ten_CTE AS (
    -- 1. Anchor Member: Lấy tầng gốc (Cấp 1)
    SELECT ID, Name, ParentID, 1 AS Level
    FROM DanhMuc
    WHERE ParentID IS NULL

    UNION ALL

    -- 2. Recursive Member: Duyệt tiếp các tầng con
    SELECT c.ID, c.Name, c.ParentID, p.Level + 1
    FROM DanhMuc c
    INNER JOIN Ten_CTE p ON c.ParentID = p.ID
)
-- Truy vấn kết quả
SELECT * FROM Ten_CTE;

4. Ví dụ thực tế: Xuất sơ đồ cấp bậc nhân sự

Giả sử bạn có bảng NhanVien chứa NhanVienID và QuanLyID. Bài toán đặt ra là xác định cấp bậc của từng nhân sự trong công ty:

WITH NhanSuPhanCap AS (
    -- Bước 1: Tìm CEO (Người không có quản lý)
    SELECT 
        NhanVienID, 
        TenNhanVien, 
        QuanLyID, 
        1 AS CapBac
    FROM NhanVien
    WHERE QuanLyID IS NULL

    UNION ALL

    -- Bước 2: Tìm nhân viên cấp dưới tương ứng
    SELECT 
        e.NhanVienID, 
        e.TenNhanVien, 
        e.QuanLyID, 
        h.CapBac + 1
    FROM NhanVien e
    INNER JOIN NhanSuPhanCap h ON e.QuanLyID = h.NhanVienID
)
SELECT 
    NhanVienID, 
    TenNhanVien, 
    QuanLyID, 
    CapBac
FROM NhanSuPhanCap
ORDER BY CapBac, NhanVienID;

5. Ứng dụng thực tế của Recursive CTE

• Duyệt cây danh mục sản phẩm: Hiển thị toàn bộ danh mục con thuộc một danh mục cha bất kỳ.

• Báo cáo sơ đồ tổ chức: Phân tích quy mô nhân sự dưới quyền của từng cấp quản lý.

• Tính toán định mức sản xuất (BOM): Tính tổng lượng nguyên vật liệu cần thiết để lắp ráp một sản phẩm phức tạp.

6. Tổng kết

Làm chủ kỹ thuật Recursive CTE trong SQL giúp Data Analyst giải quyết dễ dàng các bài toán dữ liệu phân cấp đa tầng. Đây là một trong những công cụ mạnh mẽ nhất nâng tầm tư duy xử lý cơ sở dữ liệu nâng cao.

✍️ Tác giả: Phan Thị Như Phước – Data Analyst tại MCNA Technology School

📞 Hotline: 0939.866.825 (Mr. Minh Khang)

🌐 Website: mcna.vn

📍 Hà Nội: 30 Trung Liệt, Đống Đa | Liền kề 44B TT2 Văn Quán, Hà Đông

📍 TP.HCM: 50B Phan Tây Hồ, Cầu Kiệu