CTE 是公用表表达式（Common Table Expressions）的缩写，与子查询和临时表同源。
CTE 是由 SELECT 查询派生出的临时命名结果集。它存在于 SELECT、UPDATE 或 DELETE 等外部查询的执行范围内。因为是临时的，所以在执行完成后，CTE 将消失。CTE 能够以递归的形式实现自我引用，使用 WITH 语句创建 CTE：
WITH <CTE 名称>[(逗号分隔的列名列表)] AS (
<定义 CTE 的内部查询>
)
<外部查询：SELECT|UPDATE|DELETE>
通常，在汇总数据或计算复杂公式时，需要将查询分成不同块，以使代码简洁易懂。CTE 可以在这方面提供帮助。下面的代码被分为可读性较强的内部查询和外部查询两部分。
使用 CTE 的另一个场景是分层式列表（Hierarchical List）。下文将以示例的形式展示递归式 CTE。SQL CTE 可以分为递归式和非递归式两种。非递归式 CTE 不会替换子查询、派生表或临时表。就上面的查询示例而言，每一部分都在 SQL 脚本中有着自己的空间。比如如果在另一个查询中需要临时结果集，由于临时表可以在脚本中涵盖更大的范围（比如全局范围），因此可以在各条命令中的任何位置引用它。当然，非递归式 CTE 不适用于极快的查询需求。
下面将从 8 个方面讨论如何使用 SQL CTE。
SQL CTE 支持两种形式的列别名。下面展示第一种 - 使用内联表单：
WITH invoice_cte AS (SELECT MONTH(i.invoice_date) AS invoice_month,
SUM(il.extended_price) AS amount
FROM invoice_lines il
INNER JOIN invoices i ON i.invoice_id = il.invoice_id
WHERE i.invoice_date BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY MONTH(i.invoice_date))
SELECT invoice_month, amount
FROM invoice_cte
ORDER BY invoice_month;
上述代码使用 AS 关键字定义 SQL 查询中的列别名。其中，invoice_month 和 amount 都是列别名。
下面的代码是列别名的另一种形式：
WITH invoice_cte (invoice_month, amount) AS (SELECT MONTH(i.invoice_date),
SUM(il.extended_price)
FROM invoice_lines il
INNER JOIN invoices i ON i.invoice_id = il.invoice_id
WHERE i.invoice_date BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY MONTH(i.invoice_date))
SELECT invoice_month, amount
FROM invoice_cte
ORDER BY invoice_month;
除上面使用的 SELECT 语句外，还可以使用 UPDATE、DELETE 开发 SQL CTE 实例。下面是使用 DELETE 的例子：
SET @product_id = 1;
WITH latest_product_cost AS (SELECT product_id,
start_date,
end_date,
standard_cost
FROM product_cost_history
WHERE product_id = @product_id
ORDER BY start_date DESC
LIMIT 1)
DELETE product_cost_history
FROM product_cost_history
INNER JOIN latest_product_cost
ON product_cost_history.product_id = latest_product_cost.product_id
AND product_cost_history.start_date = latest_product_cost.start_date
AND product_cost_history.end_date = latest_product_cost.end_date;
可以在一个查询中定义多个 CTE。
SET @product_id = 1;
WITH latest_product_cost AS (SELECT product_id,
start_date,
standard_cost
FROM product_cost_history
WHERE product_id = @product_id
ORDER BY start_date DESC
LIMIT 1),
previous_product_cost AS (SELECT pch.product_id,
pch.start_date,
pch.standard_cost
FROM product_cost_history pch
INNER JOIN latest_product_cost lpc ON pch.product_id = lpc.product_id
WHERE pch.product_id = @product_id
AND pch.start_date < lpc.start_date
ORDER BY pch.start_date DESC
LIMIT 1)
SELECT lpc.product_id,
p.name AS product,
lpc.standard_cost AS latest_cost,
lpc.start_date,
ppc.standard_cost AS previous_cost
FROM latest_product_cost lpc
INNER JOIN previous_product_cost ppc ON lpc.product_id = ppc.product_id
INNER JOIN product p ON lpc.product_id = p.product_id
WHERE lpc.product_id = @product_id;
为实现多次引用一个 SQL CTE，让 previous_product_cost CTE 引用 latest_product_cost CTE，外部查询再次引用 latest_product_cost CTE。
可以在存储过程中使用 SQL CTE，然后将存储过程的参数传递给它。
DROP PROCEDURE IF EXISTS insert_new_product_cost;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS insert_new_product_cost(IN product_id int, IN increase DECIMAL(18, 4))
BEGIN
INSERT INTO product_cost_history(product_id,
start_date,
end_date,
standard_cost,
modified_date)
WITH latest_product_cost AS (SELECT product_id,
start_date,
end_date,
standard_cost
FROM product_cost_history
WHERE product_id = product_id
ORDER BY start_date DESC
LIMIT 1)
SELECT product_id,
DATE_ADD(latest_product_cost.end_date, INTERVAL 1 DAY) AS start_date,
DATE_ADD(latest_product_cost.end_date, INTERVAL 366 DAY) AS end_date,
(latest_product_cost.standard_cost * increase) + latest_product_cost.standard_cost AS standard_cost,
NOW() AS modified_date
FROM latest_product_cost;
END//
DELIMITER ;
CALL insert_new_product_cost(1, 0.1);
SELECT *
FROM product_cost_history;
可以在视图中使用 SQL CTE。
CREATE VIEW yearly_invoice_totals_per_product AS
WITH invoice_cte (invoice_year, invoice_month, stock_item_id, amount) AS (SELECT YEAR(i.invoice_date),
MONTH(i.invoice_date),
il.stock_item_id,
SUM(il.extended_price)
FROM invoice_lines il
INNER JOIN
invoices i ON i.invoice_id = il.invoice_id
GROUP BY YEAR(i.invoice_date),
MONTH(i.invoice_date),
il.stock_item_id)
SELECT i.invoice_year,
i.invoice_month,
si.stock_item_name,
i.amount
FROM invoice_cte i
INNER JOIN
warehouse_stock_items si ON i.stock_item_id = si.stock_item_id;
可以将 SQL CTE 与 Cursor 一起使用，循环遍历各种结果。
DROP PROCEDURE IF EXISTS get_invoice_totals;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS get_invoice_totals()
BEGIN
DECLARE result TEXT DEFAULT '';
DECLARE invoice_month TINYINT;
DECLARE amount DECIMAL(10, 2);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
WITH cte_name AS (SELECT MONTH(invoice_date) AS invoice_month,
SUM(extended_price) AS amount
FROM invoice_lines
INNER JOIN
invoices ON invoices.invoice_id = invoice_lines.invoice_id
WHERE invoice_date BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY MONTH(invoice_date)
ORDER BY invoice_month DESC)
SELECT cte_name.invoice_month, cte_name.amount
FROM cte_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop:
LOOP
FETCH cur INTO invoice_month, amount;
IF done THEN
LEAVE read_loop;
END IF;
SET result = CONCAT(
result,
'Invoice Month: ', CAST(invoice_month AS CHAR),
', Amount: ', CAST(amount AS CHAR),
'\n');
END LOOP;
CLOSE cur;
SELECT result;
END //
DELIMITER ;
CALL get_invoice_totals();
递归式 CTE 具有一个锚成员（Anchor Member）和一个递归成员。可以使用其查询分层数据。比如组织架构就是典型的分层结构：
CREATE TABLE IF NOT EXISTS org
(
org_id BIGINT UNSIGNED NOT NULL
PRIMARY KEY,
org_name VARCHAR(1024) NOT NULL,
parent_id BIGINT UNSIGNED NULL
);
REPLACE INTO org(org_id, org_name, parent_id)
VALUES (1, 'org-1', 0),
(2, 'org-2', 1),
(3, 'org-3', 1),
(4, 'org-4', 2),
(5, 'org-5', 2),
(6, 'org-6', 3);
SET @target_org_id = 1;
WITH RECURSIVE tmp AS (SELECT org_id, org_name, parent_id
FROM org
WHERE parent_id = @target_org_id
UNION ALL
SELECT org.org_id, org.org_name, org.parent_id
FROM org
INNER JOIN tmp ON org.parent_id = tmp.org_id)
SELECT *
FROM tmp;