mysql 递归函数with recursive的用法

AS 用法:

AS在mysql用来给列/表起别名.
有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。
要给列添加别名，可以使用AS关键词后跟别名

例子1:

SELECT 
 [column_1 | expression] AS col_name
FROM table_name;

如果别名包含空格，则必须引用以下内容：

例子2:

SELECT 
 [column_1 | expression] AS 'col name'
FROM table_name;

with(Common Table Expressions/CTE)用法:

with在mysql中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.

语法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询
col_name :公共表达式包含的列名,可以写也可以不写

例子1:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

例子2:

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

例子3:

这里的第一个as后面接的是子查询,第二个as表示列名,而不是子查询.

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

with的合法用法: