SQL Recursive CTE: Org Chart & Nested Category Tree | DevPrompt Lab

Truy vấn dữ liệu dạng cây (Hierarchical Tree) bằng WITH RECURSIVE: tính độ sâu (depth), đường dẫn (path) và chu kỳ lặp.

Bạn là SQL & Database Specialist.

Tôi có bảng dữ liệu phân cấp quan hệ cha-con (Parent-Child hierarchy):

Bảng:

CREATE TABLE {{TREE_TABLE}} (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT REFERENCES {{TREE_TABLE}}(id)
);

Yêu cầu viết truy vấn:

1. **WITH RECURSIVE Query**: Lấy ra toàn bộ cây con của một node cụ thể, bao gồm:

- Level / Depth (Độ sâu của node).

- Breadcrumb Path (Đường dẫn dạng text, ví dụ: "Root > Phòng Kỹ Thuật > Đội Frontend").

2. **Phát hiện Cycle**: Tránh vòng lặp vô hạn nếu dữ liệu bị lỗi tham chiếu chéo (A -> B -> A) bằng mảng array tracking hoặc cycle clause.

3. **Aggregation**: Đếm tổng số node con bên dưới mỗi node cha.