Hướng dẫn mysql recursive cte - mysql đệ quy cte

Question

Bài viết này sẽ giới thiệu và hướng dẫn các bạn cách sử dụng MySQL recursive CTE để duyệt dữ liệu phân cấp.

Nội dung chính Show

Introduction to MySQL recursive CTE
Hạn chế của Recursive member
Simple MySQL recursive CTE example
Sử dụng MySQL recursive CTE để duyệt dữ liệu phân cấp

Chú ý rằng common table expression (CTE) chỉ được sử dụng từ MySQL phiên bản 8.0. Do đó hãy chắc chắn là bạn cài đúng version của MySQL.

Introduction to MySQL recursive CTE

recursive common table expression (CTE) là 1 CTE có subquery mà tham chiếu tới chính bản thân CTE đó. Cú pháp của recursive CTE như sau:

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

Một recursive CTE gồm 3 phần chính:

Một initial query tạo ra kết quả dựa theo cấu trúc của CTE và được coi như một anchor member.
Một recursive query part là một truy vấn tham chiếu tới chính tên của CTE và được gọi là recursive member. Recursive member join với anchor member bằng toán tử UNION ALL hoặc UNION DISTINCT.
Một điều kiện để kết thúc đệ qui khi mà recursive member không trả về row nào cả.

Thứ tự thực thi của một recursive CTE như sau:

Đầu tiên, tách biệt members thành 2: anchor và recursive members.
Tiếp theo, thực thi anchor member để tạo ra kết quả và thiết lập nó (giả sử là R0) và sử dụng kết quả đó cho lặp kế tiếp.

Sau đó, thực thi recursive member với đầu vào là Ri và tạo ra kết quả là

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

0.

Tiếp sau đó, lặp lại bước thứ 3 tới khi recursive member trả về kết quả rỗng và khi đó điều kiện kết thúc sẽ được áp dụng.

Cuối cùng, kết hợp các kết quả từ R0 tới

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

2 sử dụng UNION ALL.

Hạn chế của Recursive member

Recursive member bắt buộc không gồm các thành phần sau:

Các hàm tính trung bình như:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

4,

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

5,

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

6,

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

7,

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

8, …

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

9

SELECT n + 1
FROM cte_count 
WHERE n < 3

0

SELECT n + 1
FROM cte_count 
WHERE n < 3

1

SELECT n + 1
FROM cte_count 
WHERE n < 3

2

Các ràng buộc trên không áp dụng cho anchor member. Ngoài ra

SELECT n + 1
FROM cte_count 
WHERE n < 3

2 sẽ không được phép sử dụng khi bạn dùng

SELECT n + 1
FROM cte_count 
WHERE n < 3

4, còn trong trường hợp bạn dùng UNION DISTINCT thì

SELECT n + 1
FROM cte_count 
WHERE n < 3

2 được phép sử dụng.

Ngoải ra, recursive member chỉ có thể tham chiếu tới CTE một lần và từ mệnh đề

SELECT n + 1
FROM cte_count 
WHERE n < 3

7 chứ không phải từ subquery.

Simple MySQL recursive CTE example

See the following simple recursive CTE example:

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

Ở ví dụ này, để ý truy vấn:

là 1 anchor member trả về giá trị 1 và được set là kết quả ban đầu.

Truy vấn

SELECT n + 1
FROM cte_count 
WHERE n < 3

là recursive member vì nó tham chiếu tới tên của CTE là

SELECT n + 1
FROM cte_count 
WHERE n < 3

8.

Biểu thức

SELECT n + 1
FROM cte_count 
WHERE n < 3

9 trong recursive member là điều kiện để kết thúc lặp đệ quy. Nếu

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

0 thì recursive member trả về một tập hợp rỗng và sẽ dừng đệ quy.

Mô tả các thành phần của CTE ở trên:

Kết quả:

Hướng dẫn mysql recursive cte - mysql đệ quy cte

Các bước thực thi recursive CTE như sau:

Đầu tiên, phân tách anchor và recursive members.

Tiếp, anchor member tạo ra row ban đầu (

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

1) do đó vòng lặp đầu tiên tạo ra 1 + 1 = 2 với n = 1.

Sau đó, vòng lặp thứ 2 thực hiện trên kết quả của vòng lặp thứ nhất (2) và tạo ra 2 + 1 = 3 với n = 2.
Sau đó, trước khi vòng lặp thứ 3 thực hiện (n = 3) thì nó đã kiểm tra điều kiện kết thúc (n < 3) do đó vòng lặp kết thúc.
Cuối cùng, kết hợp tất cả các kết quả 1,2 và 3 với toán tử UNION ALL.

Sử dụng MySQL recursive CTE để duyệt dữ liệu phân cấp

Chúng ta sẽ sử dụng bảng

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

3 trong database

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

4 cho ví dụ này:

Bảng

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

3 có cột

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

6 tham chiếu tới cột

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

7. Cột

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

6 lưu các id của các quản lý. Top manager không phải báo cáo cho bất kỳ ai cho nên giá trị

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

6 là

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

0.

Bạn có thể áp dụng recursive CTE để truy vấn toàn bộ cấu trúc của tổ chức theo cách từ trên xuống như sau:

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

Để dễ hiểu hơn, ta sẽ chia nhỏ các truy vấn ra.

Đầu tiên, tạo ra anchor member:

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

Câu truy vấn này (anchor member) trả về top manager với

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

6 is

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

0.

Thứ 2, tạo ra recursive member (

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

3) bằng việc tham chiếu tới CTE:

SELECT 
    e.employeeNumber, e.reportsTo, e.officeCode
FROM
    employees e
        INNER JOIN
    employee_paths ep ON ep.employeeNumber = e.reportsTo

Truy vấn này ( recursive member) trả về tất cả các báo cáo trực tiếp của manager tới khi không còn báo cáo trực tiếp nữa. Khi đó recursion sẽ dừng lại.

Thứ 3, CTE

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

3 kết hợp tập kết quả được trả về bởi CTE với bảng

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

5 để tạo ra tập kết quả cuối cùng.

Kết quả:

Các bước thực thi recursive CTE như sau:

programming mysql WITH RECURSIVE MySQL Recursive trong SQL

Hướng dẫn mysql recursive cte - mysql đệ quy cte

Introduction to MySQL recursive CTE

Hạn chế của Recursive member

Simple MySQL recursive CTE example

Sử dụng MySQL recursive CTE để duyệt dữ liệu phân cấp

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội