gogoWebsite

This learning article is based on MySQL 8.0
Thanks to the careful guidance of the great god’s friend, I wrote this article and presented the great god with a knee.

MySQL 8 supports common table expressions, includingNon-recursiveandrecursionTwo types.
Common table expressions allow the use of named temporary result sets, which are allowed inSELECTStatements and some other statements are used beforeWITHclauses to implement.
Cannot quote twice in the same queryDerived table (subquery), because in that case, the query will be calculated twice or more based on the number of references to the derived table, which can cause serious performance problems. After using CTE, the subquery will only be calculated once.

Non-recursive CTE

1. Derived table (subquery)

select …… from ( subquery ) as derived, table_name ……;

2. CTE

with derived as (
	subquery
) 
select …… from derived, table_name ……;

CTE may beSELECT/UPDATE/DELETEBefore, includingwith derived as ( subquery )subqueries, for example:

with derived as ( 
	subquery 
) 
delete from table_name 
where table_name.col_name in (
	select col_name from derived
);

3. CTE can refer to other CTEs

with derived_one as (
	subquery
),
derived_two as (
	select …… from derived_one
)
select …… from derived_one, derived_two ……;

Recursive CTE

1. Syntax explanation

Recursive CTE is a special CTE, and its subqueries will quote their own name.WITHThe clause must beWITH RECURSIVEbeginning. Recursive CTE subquery consists of two parts:seed queryandRecursive query,Depend onUNION[ALL]orUNION DISTINCTSeparate.

• seed selectis executed once to create an initial subset of data;
• recursive selectExecution is repeated to return a subset of the data until the complete result set is obtained.
  Recursion stops when iteration does not generate any new rows. This is a digging pairHierarchy(father/son or part/child part) is very useful.

with recursive {temp_table_name}(n) as (
	select …… from table_name  /* "seed select" */
	union all 
	select …… from {temp_table_name}, table_name  /* "recursive select" */
)
select …… from derived ……;

Suppose you want to print all numbers from 1 to 5:

mysql> with recursive derived(n) as (
    -> select 1  /* "seed select" */
    -> union all 
    -> select n + 1 from derived where n < 5  /* "recursive select" */
    -> )
    -> select * from derived;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

In each iteration,SELECTA row with a new value is generated, 1 more than the value n of the previous row. The first iteration runs on the initial set of rows ( 1 ) and generates rows with a value of 1 + 1 = 2; the second iteration operates on the set of rows ( 2 ) of the first iteration and generates rows with a value of 2 + 1 = 3. And so on, it lasts until n is no longer less than 5, and the recursion ends.

2. Give a chestnut

Recursive CTE can also be used assuming that a hierarchical data traversal is performed to generate an organizational chart for each employee (i.e., the path from CEO to each employee).

• Create withmanager_idTest table

mysql> create table employees.employees_mgr ( 
    -> id int primary key not null,
    -> name varchar(100) not null,
    -> manager_id int null,
    -> index (manager_id),
    -> foreign key (manager_id) references employees_mgr (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

• Insert sample data

mysql> insert into employees.employees_mgr values 
    -> (333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
    -> (198, "John", 333), /* John has id 198 and reports to 333 (Yasmina) */
    -> (692, "Tarek", 333),
    -> (29, "Pedro ", 198),
    -> (4610, "Sarah", 29),
    -> (72, "Pierre", 29),
    -> (123, "Adil", 692);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select id, name, manager_id from employees.employees_mgr;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+
7 rows in set (0.00 sec)

• Execute recursive CTE

mysql> with recursive employee_paths (id, name, path) as (
    -> select id, name, cast(id as char(200))
    -> from employees.employees_mgr
    -> where manager_id is null
    -> union all
    -> select , , concat(, '->', )
    -> from employee_paths as ep
    -> join employees.employees_mgr as e
    -> on  = e.manager_id
    -> )
    -> select * from employee_paths;
+------+---------+--------------------+
| id   | name    | path               |
+------+---------+--------------------+
|  333 | Yasmina | 333                |
|  198 | John    | 333->198           |
|  692 | Tarek   | 333->692           |
|   29 | Pedro   | 333->198->29       |
|  123 | Adil    | 333->692->123      |
|   72 | Pierre  | 333->198->29->72   |
| 4610 | Sarah   | 333->198->29->4610 |
+------+---------+--------------------+
7 rows in set (0.00 sec)

  with recursive employee_paths (id, name, path) asis the name of the CTE, the column is(id, name, path)。
  select id, name, cast(id as char(200)) from employees.employees_mgr where manager_id is nullQuery the CEOseed query(No manager above CEO).
  select , , concat(, '->', ) from employee_paths as ep join employees.employees_mgr as e on = e.manager_idIt is a recursive query.
Each row generated by the recursive query will look for all employees who report directly to the employees generated by the previous row. For each employee, the information of the bank includes the employee ID, name, and employee management chain, which is the management chain with the employee ID added at the end.