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;
Mysql CTE 语法
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 的语法
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的执行顺序
- 将cte划为为两个部分anchor成员 和 recursive成员
- 执行anchor 部分,形成结果集R0, 在下次遍历中,以此为作为R1的基础结果集
- 执行 recursive部分 Ri作为输入结果集(比如第一次执行recursive时,它为R0),并输入Ri+1
- 递归到终此条件 recursive返回空结果集
- 通过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