简述
With语句可以出现在SELECT、INSERT、DELETE和UPDATE语句的开头,如下。
其中,common-table-expression
可以简写为cte
。
上面内容的原型就是With语句。它的命令图如下:
cte-table-name
命令图中cte-table-name
其实就是表名和其中部分列名的组合,命令图如下:
如果在INSERT语句中,不填写schema-name
和alias
,则TO
后面就跟着一个cte-table-name
,如下面代码中,insert_study_table(integer_value, real_value, text_value)
就是一个cte-table-name
。
INSERT INTO insert_study_table(integer_value, real_value, text_value) VALUES (1, 1.1, 'a');
简单用法
假设有数据表for_test_table
,存有如下数据:
id | integer_value | real_value | text_value | blob_value |
---|---|---|---|---|
1 | 1 | 1.1 | a | a |
2 | 2 | 1.2 | b | NULL |
则执行下面命令 |
WITH sel AS
(
SELECT integer_value, real_value FROM for_test_table
UNION ALL VALUES (1, 2.1), (2, 2.2)
)
SELECT * FROM sel;
输出结果为
integer_value | real_value |
---|---|
1 | 1.1 |
2 | 1.2 |
1 | 2.1 |
2 | 2.2 |
With
语句可以查询出一些数据,并提供给后面的命令使用。
RECURSIVE
RECURSIVE
的意思是递归,标记了RECURSIVE
也可以跟随普通(非递归)表达式。
先给出一个简单的使用递归表达式的With语句示例:
WITH RECURSIVE
ctn(integer_value) AS (VALUES(1) UNION ALL SELECT integer_value+1 FROM ctn WHERE integer_value<3)
SELECT integer_value FROM ctn;
结果为
integer_value |
---|
1 |
2 |
3 |
上面示例命令中,声明cte-table-name
为ctn(integer_value)
,select-stmt
是一个复合语句,先指定一个元素1
,最为迭代起点,然后从ctn中迭代地查出元素并加一,得到新的元素,并通过UNION ALL添加到结果中。迭代在integer_value为3时结束。
再给出一个较为常见的用法:
假设有数据表with_clause_study_table
,内有数据如下
id | integer_value | real_value | text_value |
---|---|---|---|
1 | NULL | NULL | CEO |
2 | 1 | NULL | COO |
3 | 1 | NULL | CTO |
4 | 2 | NULL | SVP Finance |
5 | 2 | NULL | SVP HR |
6 | 2 | NULL | EVP Operations |
7 | 3 | NULL | VP Engineering |
8 | 3 | NULL | VP IT |
9 | 3 | NULL | VP Product Management |
10 | 6 | NULL | VP Product Development |
integer_value
列的数据代表了该行上级的id
,则结构如下
- CEO
- COO
- SVP Finance
- SVP HR
- EVP Operations
- VP Product Development
- VP Manufacturing
- VP Logistics
- CTO
- VP Engineering
- VP IT
- VP Product Management
- Product Manager A
- Product Manager B
- Product Manager C
- COO
如下命令可以查出COO为根节点的树元素。
WITH RECURSIVE ctn(id, integer_value, text_value) AS
(
SELECT id, integer_value, text_value FROM with_clause_study_table WHERE id=2
UNION ALL
SELECT with_clause_study_table.id, with_clause_study_table.integer_value, with_clause_study_table.text_value FROM ctn,with_clause_study_table WHERE with_clause_study_table.integer_value=ctn.id
)
SELECT * FROM ctn ORDER BY id;
结果为:
id | integer_value | text_value |
---|---|---|
2 | 1 | COO |
4 | 2 | SVP Finance |
5 | 2 | SVP HR |
6 | 2 | EVP Operations |
10 | 6 | VP Product Development |
11 | 6 | VP Manufacturing |
12 | 6 | VP Logistics |
基本要求
设递归Select
语句为从cte-table
中查询的语句;否则为非递归Select
语句。
使用递归表达式有如下几个要求
select-stmt
必须是复合SELECT语句
,即由不少于2个独立的SELECT
连接组成,连接关键字有UNION
,UNION ALL
,INTERSECT
和EXCEPT
。- 至少有一个
递归Select
语句。 - 至少有一个
非递归Select
语句。 - 所有
非递归Select
语句都写在递归Select
语句之前 递归Select
语句前面的分隔符必须是UNION
或者UNION ALL
。且所有递归Select
语句前面的分隔符必须一致。- 不从
cte-table
中查询的语句不能使用Aggregate函数和窗口函数
递归CTE命令图的命令图如下
由命令中带RECURSIVE
时,将cte-table-name
命名的数据表记为递归表
(recursive table)
CTE命令图中命令的执行大致逻辑如下:
- 执行
非递归Select
语句,并将结果存入队列。 - 只要队列非空,则不断循环。
- 从队列中取出一个元素(一行数据)
- 将这个元素插入
递归表
中(这一步受到UNION,LIMIT和OFFSET的限制) - 假设
递归表
中只有刚插入的这一行数据(实际没有插入也可以假设有),对递归表
执行递归Select
语句,并将结果存入队列。
以查出COO为根节点的树元素的示例代码为例,队列和递归表中数据的变化顺序如下表所示
队列 | 递归表 | 说明 |
---|---|---|
COO | 非递归Select命令查出COO,并入队 | |
COO | 出队入表 | |
SVP Finance SVP HR EVP Operations | COO | 假设只有COO一条数据(这里也是事实),用递归Select命令查询,查出三条结果,并入队 |
SVP HR EVP Operations | COO SVP Finance | SVP Finance出队。 假设只有SVP Finance一条数据,用递归Select命令查询,没有符合要求的结果 |
EVP Operations | COO SVP Finance SVP HR | |
COO SVP Finance SVP HR EVP Operations | EVP Operations出队 | |
`VP Product Development VP Manufacturing VP Logistics | COO SVP Finance SVP HR EVP Operations | 假设只有EVP Operations一条数据,用递归Select命令查询,查到3条符合要求的数据 |
VP Manufacturing VP Logistics | COO SVP Finance SVP HR EVP Operations VP Product Development | |
VP Logistics | COO SVP Finance SVP HR EVP Operations VP Product Development VP Manufacturing | |
COO SVP Finance SVP HR EVP Operations VP Product Development VP Manufacturing VP Logistics | VP Logistics出队。 假设只有VP Logistics一条数据,用递归Select命令查询,没有符合要求的结果。 此时队列为空而且没有新元素的补充,递归查询结束 |
Materialization
With语句中AS关键字后面,可以填写NOT MATERIALIZED
或者MATERIALIZED
或者两个都不填。一般推荐两个都不填,这样SQLite可以自己判断更优的选择。
MATERIALIZED
表示将With中的查询结果存储在临时表中,后续多次用到时可以直接从临时包中获取数据;NOT MATERIALIZED
表示将With语句的查询当做一次subquery,后续用到查询结果的地方都做一次Subquery。
九宫格数独问题
官网给出了一个用With语句解决数独问题的方法。假设有数独问题如下:
5 | 3 | 7 | ||||||
---|---|---|---|---|---|---|---|---|
6 | 1 | 9 | 5 | |||||
9 | 8 | 6 | ||||||
8 | 6 | 3 | ||||||
4 | 8 | 3 | 1 | |||||
7 | 2 | 6 | ||||||
6 | 2 | 8 | ||||||
4 | 1 | 9 | 5 | |||||
8 | 7 | 9 |
执行如下命令
WITH RECURSIVE
input(sud) AS (
VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0;
得到结果:
s |
---|
534678912672195348198342567859761423426853791713924856961537284287419635345286179 |
即
5 | 3 | 4 | 6 | 7 | 8 | 9 | 1 | 2 |
6 | 7 | 2 | 1 | 9 | 5 | 3 | 4 | 8 |
1 | 9 | 8 | 3 | 4 | 2 | 5 | 6 | 7 |
8 | 5 | 9 | 7 | 6 | 1 | 4 | 2 | 3 |
4 | 2 | 6 | 8 | 5 | 3 | 7 | 9 | 1 |
7 | 1 | 3 | 9 | 2 | 4 | 8 | 5 | 6 |
9 | 6 | 1 | 5 | 3 | 7 | 2 | 8 | 4 |
2 | 8 | 7 | 4 | 1 | 9 | 6 | 3 | 5 |
3 | 4 | 5 | 2 | 8 | 6 | 1 | 7 | 9 |
这个语句的效率不一定高,比如数独问题为.........59.8..7......218...37.......5.79........3.18...5..2...81.....4...6.8.9.3
时,耗时就会明显增加。
- 题目:
5 | 9 | 8 | 7 | |||||
2 | 1 | 8 | ||||||
3 | 7 | |||||||
5 | 7 | 9 | ||||||
3 | 1 | 8 | ||||||
5 | 2 | |||||||
8 | 1 | 4 | ||||||
6 | 8 | 9 | 3 |
- 答案
6 | 8 | 2 | 4 | 5 | 7 | 3 | 9 | 1 |
---|---|---|---|---|---|---|---|---|
5 | 9 | 1 | 8 | 6 | 3 | 7 | 2 | 4 |
3 | 7 | 4 | 9 | 2 | 1 | 8 | 5 | 6 |
2 | 3 | 7 | 1 | 4 | 8 | 5 | 6 | 9 |
1 | 5 | 8 | 7 | 9 | 6 | 4 | 3 | 2 |
4 | 6 | 9 | 2 | 3 | 5 | 1 | 8 | 7 |
9 | 4 | 5 | 3 | 1 | 2 | 6 | 7 | 8 |
8 | 1 | 3 | 6 | 7 | 9 | 2 | 4 | 5 |
7 | 2 | 6 | 5 | 8 | 4 | 9 | 1 | 3 |