http://www.mysqltutorial.org/mysql-cte/

CTE 是一个临时的结果集，存在于单个SQL语句的执行上下文, 如 SELECT, INSERT, Update, DELETE, 跟派生表, 类似于 derived table(派生表, 查询中的子查询), CTE不会保存为一个对像，只是返回最后一个查询。不同于 derived table, CTE 可以引用自已, 甚至可以在多次引用, 同时cte提供了更好的可读性和性能。

# 派生表
SELECT Column_list
FROM (
	# 派生表，查询中的子查询
	SELECT column_list
	FROM table_1
	) derived_table_name # 必须有一个别名
WHERE derived_table_name.c1 > 0;

cte 包含一个名称， 一个可选的列表(id, name, password), 和一个查询语句。在CTE定义以后，你可以将它用于SELECT, INSERT, UPDATE, DELETE, 或者CREATE VIEW语句中

WITH cte_name (column_list) AS (
    query
) # 这里不需要会分号，只当一条语句执行
SELECT * FROM cte_name;

column_list的列数必须跟query一样

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'  # 限定国家为USA
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

在这里有两个cte, 第一个CTE获取员工为’Sales Rep’ title的员工， 第二个cte引用第一个CTE.

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

也可以用于derived table的子查询中

SELECT ... WHERE id IN (WITH ... SELECT ...);
 
SELECT * FROM (WITH ... SELECT ...) AS derived_table;

其它

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

树，或者我们保存文件夹路径时，可以通过 with recursive

基本语法

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;

recursive包含以下三个部分

• 初始化查询，它构成了整个查询结果的基本框架，即返回的列名
• recursive query部分，引用cte_name, 它称为递归成员
• 终此条件, 使得第二部分返回空行
  recursive的执行顺序

1. 将cte划为为两个部分anchor成员 和 recursive成员
2. 执行anchor 部分，形成结果集R0, 在下次遍历中，以此为作为R1的基础结果集
3. 执行 recursive部分 Ri作为输入结果集(比如第一次执行recursive时，它为R0)，并输入Ri+1
4. 递归到终此条件 recursive返回空结果集
5. 通过UNION ALL 操作符，组合R0到Rn所以的结果集

recursive部分不能包含以下的结构

汇总函数， MAX, MIN, SUM, AVG, COUNT
GROUP BY 子句
ORDER BY
LIMIT
DISTINCT

DISTINCE可以使用UNION DISTINCT操作符

另外，recursive成员在它的FROM子句中引用一次，但不能是在子查询部分

with recursive cte_count(n) # n表示列的名称, 类似于定义表， 这后可以通过这个名称，获取相要的数据
as (
	select 1 # initial anchor member
	UNION ALL
	select n+1  # recursive member
	FROM cte_count
	WHERE n < 3
	)
	select n FROM cte_count;

查询id为1的树

CREATE TABLE `spec_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
)

WITH RECURSIVE cte_tree AS (
	select id, `name`, parent_id from spec_data where id=1
	UNION ALL
	select spec_data.id, concat(b.`name`, '/', spec_data.`name`), spec_data.parent_id from spec_data INNER JOIN cte_tree as b ON spec_data.parent_id=b.id
) select id, `name`, parent_id from cte_tree