【SQLite学习笔记】With语句

简述

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为根节点的树元素。

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语句。
使用递归表达式有如下几个要求

1. select-stmt必须是复合SELECT语句，即由不少于2个独立的SELECT连接组成，连接关键字有UNION，UNION ALL，INTERSECT和EXCEPT。
2. 至少有一个递归Select语句。
3. 至少有一个非递归Select语句。
4. 所有非递归Select语句都写在递归Select语句之前
5. 递归Select语句前面的分隔符必须是UNION或者UNION ALL。且所有递归Select语句前面的分隔符必须一致。
6. 不从cte-table中查询的语句不能使用Aggregate函数和窗口函数

递归CTE命令图的命令图如下

由命令中带RECURSIVE时，将cte-table-name命名的数据表记为递归表（recursive table）
CTE命令图中命令的执行大致逻辑如下：

1. 执行非递归Select语句，并将结果存入队列。
2. 只要队列非空，则不断循环。
   1. 从队列中取出一个元素（一行数据）
   2. 将这个元素插入递归表中（这一步受到UNION，LIMIT和OFFSET的限制）
   3. 假设递归表中只有刚插入的这一行数据（实际没有插入也可以假设有），对递归表执行递归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