文章目录

• 一、CTE特性
• 1.CTE的优点
• 2.CTE的使用场景
• 3.CTE的语法
• 二、SQL递归树状结构Demo
• 1.创建表格
• 2.使用SQL递归-依据父节点、查找所有子节点
• 3.使用SQL递归-依据子节点查找所有父节点

一、CTE特性

从SQL Server 2005开始，提供了CTE(Common Table Expression，公用表表达式) 的语法支持。
CTE是定义在SELECt、INSERT、UPDATE或DELETE语句中的临时命名的结果集，同时CTE也可以用在视图的定义中。
在CTE中可以包括对自身的引用，因此这种表达式也被称为递归CTE。

1.CTE的优点

公用表表达式提供的功能其实和视图差不多，但是它不像视图一样把SQL语句保存在我们的数据库里面。
微软官方给出的使用CTE的优势：

1.可以编写一个递归查询。
2.要使用一个类似视图的功能，但是又不想把这个查询SQL语句的定义保存到数据库中。
3.要引用一个返回数据的SQL语句多次，只需要定义一次。
使用CTE可以把复杂的SQL语句按照逻辑分成简单独立的几个公用表表达式（CTE），这样的最大优势就是能够提高SQL语句的可读性和可维护性。

总结就是，CTE主要可以用于树结构的递归和简化SQL语句，增加可读性和可维护性。

2.CTE的使用场景

由于业务需要，我们经常会写一些比较复杂的SQL语句，里面可能会包含很多的JOIN或子查询，要维护和理清这种N多个表的JOIN关系是一件非常头疼的事情，而使用CTE就可以使维护和理解复杂的SQL语句变得简单一些。
在开发的时候使用子查询，一般是这种情况：需要从一个复杂的子查询，甚至多级子查询嵌套。在这种情况下，在整个SQL语句里面，无论你是直接写SQL语句还是把这段SQL语句包装成子查询然后用别名来访问，当业务需求越来越变得复杂，你可能随时需要修改这个长且复杂的SQL语句段，而维护这种复杂的、可读性差的SQL语句简直是噩梦。
有了CTE只有，我们就可以使用CTE来定义一个SQL语句，并且为这个SQL语句执行后返回的结果集定义一个别名，接下来就可以通过这个别名来引用这些预先执行返回的数据集，就像使用普通的表一样。

3.CTE的语法

一个公用表表达式主要包含三个主要部分：

1.CET名称（WITH后面，列名列之前）。
2.列名列（可选）。
3.CET查询语句主体（AS后面括起来的内容）。

二、SQL递归树状结构Demo

1.创建表格

CREATE TABLE category_Organization
(
	[ID] INT NOT NULL IDENTITY(1,1),--主键
    [GUID] INT NOT NULL,  --业务ID(唯一标识)
	[OrgCode] NVARCHAR(50) NULL, --机构编号
	[OrgName] NVARCHAR(50) NULL, --机构名称
	[OrgPGUID] NVARCHAR(50) NULL,--上级机构
	[ilevel] int null--级别
)
GO
 INSERT category_Organization VALUES(1,'a1','中国电信','0',1)
 INSERT category_Organization VALUES(2,'b1','中国电信陕西分公司','1',2)
 INSERT category_Organization VALUES(3,'c1','中国电信西安分公司','2',3)
 INSERT category_Organization VALUES(4,'d1','中国电信高新营业厅','3',4)
 INSERT category_Organization VALUES(5,'e1','高新营业客服部','4',5)
  INSERT category_Organization VALUES(6,'a2','中国移动','0',1)
 INSERT category_Organization VALUES(7,'b2','中国移动陕西分公司','6',2)
 INSERT category_Organization VALUES(8,'c2','中国移动西安分公司','7',3)
 INSERT category_Organization VALUES(9,'d2','中国移动高新营业厅','8',4)
 INSERT category_Organization VALUES(10,'e2','高新营业客服部','9',5)
SELECT *FROM category_Organization

2.使用SQL递归-依据父节点、查找所有子节点

放入存储过程中

CREATE PROCEDURE Child_ById
@id int --唯一标识
AS
BEGIN
with cte as
(
select category_Organization.GUID,OrgName,OrgPGUID from category_Organization where category_Organization.GUID=@id
union all
select a.GUID,a.OrgName,a.OrgPGUID from category_Organization a join cte b on a.OrgPGUID = b.GUID
)
select * from cte order by GUID asc
end
--EXEC Child_ById 1

3.使用SQL递归-依据子节点查找所有父节点

CREATE PROC Parent_ById
@id int --唯一标识
AS
BEGIN
WITH CTE(GUID,OrgPGUID,OrgName,ilevel)
 AS
(
	SELECt GUID,OrgPGUID,OrgName,ilevel FROM category_Organization WHERe GUID=@id UNIOn ALL
	SELECt B.GUID,B.OrgPGUID,B.OrgName,B.ilevel FROM CTE A,category_Organization B WHERe A.OrgPGUID=B.GUID
)
SELECt GUID,OrgPGUID,OrgName,ilevel FROM CTE order by ilevel asc
end
--exec Parent_ById 10

参考：https://www.cnblogs.com/lonelyxmas/p/10652423.html