CTE
从版本8.0开始,MySQL简单地引入了公用表表达式功能(CTE)。
公共表表达式是只存在一个单一的SQL语句例如执行范围内的一个命名的临时结果集,如:SELECT,INSERT,UPDATE,或DELETE。
与派生表类似,CTE不作为对象存储,仅在执行查询期间持续存在。与派生表不同,CTE可以是自引用(递归CTE),也可以在同一查询中多次引用。此外,与派生表相比,CTE提供了更好的可读性和性能。
语法:
WITH cte_name (column_list) AS (
query
)
SELECT * FROM cte_name;
示例:
WITH customers_in_usa AS (
SELECT
customerName, state
FROM
customers
WHERE
country = '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;
with子句的用法:
有一些上下文可以使用WITH子句来创建公用表表达式:
WITH子句可以在开始时使用SELECT,UPDATE和DELETE语句:
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
WITH子句可以在子查询或派生表子查询的开头使用:
SELECT ... WHERE id IN (WITH ... SELECT ...);
SELECT * FROM (WITH ... SELECT ...) AS derived_table;
WITH可以在SELECT包含SELECT子句的语句之前使用子句:
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...