前端之家收集整理的这篇文章主要介绍了
SQLite中使用CTE巧解多级分类的级联查，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
http://www.cnblogs.com/powertoolsteam/p/sqlite.html
在最近的项目中使用ActiveReports报表设计器设计一个报表模板时，遇到一个多级分类的难题：需要将某个部门所有销售及下属部门的销售金额汇总，因为下属级别的层次不确定，所以靠拼接子查询的方式显然是不能满足要求，经过一番实验，利用了CTE（Common Table Expression）很轻松解决了这个问题！
举例：有如下的部门表
以及员工表
如果想查询所有西北区的员工（包含西北、西安、兰州），如下图所示：
如何用CTE的方式实现呢？
Talk is cheap. Show me the code
-- 以下代码使用sqlite 3.18.0 测试通过
WITH
[depts](dept_id]) AS(
SELECT d].]
FROM dept] ]
JOIN employeese] ON ] = WHERE emp_name'西北-经理'
UNION ALL
sparent_id]
)
SELECT *
]
IN (]
]);
@H_
404_32@
我还是更喜欢称CTE（Common Table Expression）为“公用表变量”而不是“公用表达式”，因为从行为和使用场景上讲，CTE更多的时候是产生（分迭代或者不迭代）结果集，供其后的语句使用（查询、插入、删除或更新），如上述的例子就是一个典型的利用迭代遍历树形结构数据。
WITH
RECURSIVE
xaxis(x) AS (
VALUES(
-2.0)
ALL SELECT x
+0.05 FROM xaxis
WHERE x
<1.2),yaxis(y) 1.0)
SELECT y
0.1 FROM yaxis
WHERE y
1.0),m(iter,cx,cy,x,y) AS (
0,y,
0.0,0); line-height:1.5!important">
0.0 FROM xaxis,yaxis
ALL
SELECT iter
1,x
*x
-y
*y
+ cx,0); line-height:1.5!important">
2.0+ cy
FROM m
WHERE (x
*x
+ y
*y)
< 4.0 AND iter
28
),m2(iter,cy) max(iter),cy
FROM m
GROUP BY cx,cy
),a(t) SELECT group_concat( substr(
.+*#',0); line-height:1.5!important">
1+min(iter
/7,0); line-height:1.5!important">
4),0); line-height:1.5!important">
1),
'')
FROM m2
BY cy
)
SELECT group_concat(
rtrim(t),x
0a')
FROM a;
@H_
404_32@
运行后的结果，如下图：（使用sqlite Expert Personal 4.2 x64）
数独问题（Sudoku）
假设有类似下图的问题：
WITH
RECURSIVE
input(sud) VALUES(
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),digits(z,lp) 11)
SELECT
CAST(lp
1 AS TEXT),lp
FROM digits
WHERE lp
9
),x(s,ind) SELECT sud,instr(sud,0); line-height:1.5!important">.
FROM input
SELECT
substr(s,1)
|| z
|| substr(s,0); line-height:1.5!important">
1),instr( substr(s,0); line-height:1.5!important">' )
FROM x,digits
AS z
WHERE ind
>0
AND NOT EXISTS (
1
AS lp
WHERE z.z
= substr(s,((ind
1)
9)
*9 + lp,0); line-height:1.5!important">
1)
OR z.z
%9)
+ (lp
+ 3)
% * 3
+ ((ind
27)
27 + lp
+ ((lp
/ 6,0); line-height:1.5!important">
1)
)
)
SELECT s
FROM x
=0;
@H_
404_32@
执行结果（结果中的数字就是对应格子中的答案）
附：sqlite中CTE（WITH关键字）语法图解：
WITH
cte-table-name
Select-stmt:
总结
CTE是解决一些特定问题的利器，但了解和正确的使用是前提，在决定将已有的一些sql重构为CTE之前，确保对已有语句有清晰的理解以及对CTE足够的学习！Good Luck~~~
