#database #sql
- blog: https://learnsql.com/blog/what-is-common-table-expression/
- course: [https://learnsql.com/course/common-table-expressions/](https://learnsql.com/course/common-table-expressions/)
# Intro
- CTE：common table expression，经常被简称为 `WITH` clause，本质上是 named subquery。
- 其结果存储在一个 virtual table（CTE）中，可以供主查询引用。
- 用途：将 long query 拆分成小的片段，增强可读性。
- 与 subquery 的区别：CTEs 可以递归（**recursive**）的，
-----------
# CTEs Help Simplify Queries
```sql
WITH my_cte AS (
SELECT a,b,c
FROM t1
)
SELECT a,c FROM my_cte WHERE ....
```
- 可以定义两个到多个 CTEs
```sql
WITH london1_monthly_revenue AS (
SELECT
EXTRACT(MONTH FROM date) as month,
SUM(unit_price * quantity) AS revenue
FROM sales
WHERE EXTRACT(YEAR FROM date) = 2021
AND branch = 'London-1'
GROUP BY 1
),
london2_monthly_revenue AS (
SELECT
EXTRACT(MONTH FROM date) as month,
SUM(unit_price * quantity) AS revenue
FROM sales
WHERE EXTRACT(YEAR FROM date) = 2021
AND branch = 'London-2'
GROUP BY 1
)
SELECT
l1.month,
l1.revenue + l2.revenue AS london_revenue,
l1.revenue AS london1_revenue,
l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month
```
-------
# Nested CTEs
```SQL
WITH over_90_items AS (
SELECT DISTINCT
item,
unit_price
FROM sales
WHERE unit_price >=90
),
london2_over_90 AS (
SELECT
o90.item,
o90.unit_price,
coalesce(SUM(s.quantity), 0) as total_sold
FROM over_90_items o90
LEFT JOIN sales s
ON o90.item = s.item AND s.branch = 'London-2'
GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM london2_over_90;
```
- 第一个 CTE 是 `over_90_items`
- 第二个 CTE 是 `london2_over_90`，它引用了第一个 CTE。
--------
# Recursive Queries
- `RECURSIVE` 是一个预留关键字用来定义遍历递归数据结构的 CTE。
```sql
WITH RECURSIVE cte_name AS (
CTE_query_definition -- non recursive query term
UNION ALL
CTE_query_definition -- recursive query term，如何产生新纪录
)
SELECT * FROM cte_name;
```
- 菜单例子：每个 menu node 都有自己的父节点
```sql
create table menu_node(id int, parent_node_id int, seq int, name text, url_path text);
insert into menu_node values
( 1 , NULL , 1 , 'root', NULL),
( 2 , 1 , 1 , 'Diagram' , 'diagram'),
( 3 , 1 , 2 , 'My models' , 'my_models'),
( 4 , 1 , 3 , 'Share requests' , 'share'),
( 5 , 1 , 4 , 'My account' , 'account'),
( 6 , 1 , 5 , 'Invite people' , 'invite'),
( 7 , 1 , 6 , 'Help' , 'help'),
( 8 , 7 , 1 , 'Documentation' , 'doc'),
( 9 , 7 , 2 , 'FAQ' , 'faq'),
(10 , 7 , 3 , 'Ask a question' , 'ask'),
(11 , 7 , 4 , 'Request a feature' , 'feature'),
(12 , 7 , 5 , 'Report a problem' , 'problem'),
(13 , 7 , 6 , 'Keyboard shortcuts' , 'shortcuts'),
(14 , 8 , 1 , 'Section 1' , 'section1'),
(15 , 8 , 2 , 'Section 2' , 'section2'),
(16 , 8 , 3 , 'Section 3' , 'section3');
```
```sql
WITH RECURSIVE menu_tree
(id, name, url, level, parent_node_id, seq)
AS (
-- 获取 root node
SELECT
id,
name,
'' || url_path,
0,
parent_node_id,
1
FROM menu_node
WHERE parent_node_id is NULL
UNION ALL
-- 递归查询，自增 level
SELECT
mn.id,
mn.name,
mt.url || '/' || mn.url_path,
mt.level + 1,
mt.id,
mn.seq
FROM menu_node mn, menu_tree mt
WHERE mn.parent_node_id = mt.id
)
-- 获取除了 root 之外的所有 rows
SELECT * FROM menu_tree
WHERE level > 0
ORDER BY level, parent_node_id, seq;
```
- [ ] 每次递归是计算 menu_node 最新的所有数据，还是只计算上次新增的？
- 如果是所有，则需要考虑去重
---
# 其他引用
- [MySQL · 新特性分析 · CTE执行过程与实现原理](https://developer.aliyun.com/article/71981)