MySQL 8.0 WITH query details

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
 
+----------------------------+----------------------+
| MAX(txt) | MIN(txt) |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
 
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0,00 sec)

This specifies the temporary table column name in the first row WITH RECURSIVE my_cte(a,b,c) AS
(
  SELECT 1,1,1
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;
 
The first row does not specify column names, and the column names are determined by the results returned by the first query WITH RECURSIVE my_cte AS
(
  SELECT 1 AS a,1 AS b,1 AS c
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;

WITH RECURSIVE cte_name [list of column names ] AS
(
  SELECT ... <-- specifies initial set
  UNION ALL
  SELECT ... <-- specifies initial set
  UNION ALL
  ...
  SELECT ... <-- specifies how to derive new rows
  UNION ALL
  SELECT ... <-- specifies how to derive new rows
  ...
)
[, any number of other CTE definitions ]

 
CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, 'Zhejiang Province');
INSERT INTO tb VALUES('001', 0, 'Guangdong Province');
INSERT INTO tb VALUES('003', '002', 'Quzhou City');
INSERT INTO tb VALUES('004', '002', 'Hangzhou');
INSERT INTO tb VALUES('005', '002', 'Huzhou City');
INSERT INTO tb VALUES('006', '002', 'Jiaxing City');
INSERT INTO tb VALUES('007', '002', 'Ningbo City');
INSERT INTO tb VALUES('008', '002', 'Shaoxing City');
INSERT INTO tb VALUES('009', '002', 'Taizhou City');
INSERT INTO tb VALUES('010', '002', 'Wenzhou City');
INSERT INTO tb VALUES('011', '002', 'Lishui City');
INSERT INTO tb VALUES('012', '002', 'Jinhua City');
INSERT INTO tb VALUES('013', '002', 'Zhoushan City');
INSERT INTO tb VALUES('014', '004', 'Uptown');
INSERT INTO tb VALUES('015', '004', 'Downtown');
INSERT INTO tb VALUES('016', '004', 'Gongshu District');
INSERT INTO tb VALUES('017', '004', 'Yuhang District');
INSERT INTO tb VALUES('018', '011', 'Jindong District');
INSERT INTO tb VALUES('019', '001', 'Guangzhou');
INSERT INTO tb VALUES('020', '001', 'Shenzhen City');
 
WITH RECURSIVE cte AS (
 SELECT id,name FROM tb WHERE id='002'
 UNION ALL
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid
) SELECT * FROM cte;