SQL server数据库with as子句与递归查询的实现

sql server中递归并不与Oracle等数据库中的递归一样。在sql server中递归使用join自连接完成的，当然这些都要在with as短句中完成的，下面我们先来说一下with as 短句。

1.关于with as短句

WITH AS短语，也叫子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

with cet as(
    select id name,age from stuents where Name like '刘%' 
)
--因为with as是一种子句形式，所以它的使用必须依赖于主查询语句，否则会报错
select * from cet
 
/*
**当然了，我是更喜欢这种形式去写with as子句，
**这样看起来更加清晰明了
**
with cet(id,name,age)as(
    select id name,age from stuents where Name like '刘%' 
)
*/

2.递归查询使用

WITH cte(id,name,pid,sorder,slevel)AS(
--先找到递归的起点
SELECT id,name,pid,shoe_order as sorder,1 as slevel  FROM tree_demo WHERE pid=0
--用join从起点开始连接形成递归查询，然后用union all拼接上起点
UNION ALL
SELECT b.id,b.name,b.pid,b.shoe_order as sorder,c.slevel+1 as slevel  FROM tree_demo b
JOIN cte c on b.pid=c.id
)
SELECT * FROM cte

 例如，这张表应该是一个树的表格，我们现在运用递归查询，要将树的每一个节点标注上节点层级序号，查出这张表的数据以及每条数据对应树节点的层级(例如：世界层级是1，中国、日本的树层级是2)。

首先我先找到树的根节点(节点名称是'世界'的节点，也就是pid=0)，作为本次递归查询的起点，然后查询表格并将表格数据与子句查询结果做关联查询从而形成递归，在最后将根节点与递归节点用union all拼接到一起，这便是递归查询的最后结果了。

版权声明：本文为CSDN博主「一片萧瑟地落叶」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_39818210/article/details/82255781