sql递归查询所有子节点_数据库大师成长日记：再论通过SQL实现树形递归查询

朋友们，在上一讲中，我们通过使用公用表表达式的递归调用，实现了部门员工数据的递归查询，数据虽然分了层级，但却没有得到直观的树形结构。这一讲我们就更深一步，将部门员工数据生成一棵带有层级的关系树。

我们先来温习一下上一讲中的处理方式，上一讲中，我们通过定义表变量存储部门员工数据，通过公用表表达式实现递归调用。限定的条件是表变量的上级内码等于基础查询的内码，从而实现了数据之间的关联关系。

脚本和运行结果具体参看下图：

上图中脚本中的方框是重点，这里实现了公用表表达式的递归调用。查询结果确实进行了梳理，而且我们还造出了一个FLevel字段把层级理了出来。但这毕竟不是我们想要的那棵部门员工树。

FLevel描述的层级字段很重要，有了这个层级，我们就可以在FName前面根据层级加一些标识符号表达名称的层级。名称的层级表达可使用如下脚本实现：

replicate('.',FLevel*2)+FName

这样一个层级就映射了一套“..”号。

生成树形最难的能否找到一个有效的排序字段，现有的字段中能够反应层级的只有FLevel，这显然是无法作为排序字段，它只能够将层级排出来。我们还需要有一个能够反应层级关系的字段，这是最难搞的。

换一个角度来看，层级关系其实就是FDataID形成的一个链表，比如张三，她的FDataID是“2”，她的上级内码是“1”，再上级就没了，那么张三的层级链表就是“1.2”；同理，李四的层级链表是“1.4”，张三和李四的链表都是“1”开头的；再同理销售一部的层级链表是“1”。不知您是否看出来了，“1”开头的就是销售一部的。如果我们能够把所有数据层级链表都生成，那么通过层级链表进行排序就很自然的形成了层级树。下图是我们设想的结果：

那么如何生成层级链表呢？其实也并不复杂，在基础查询部分，我们将FDataID强制转换为nvarchar类型作为层级链表，在递归部分，我们用上层的层级链表加上本级的FDataID，就能够直接生成本级的层级链表。可以用如下脚本实现：

基础查询部分：cast(+FDataID as nvarchar(max))递归调用部分：上级层级链表+cast(FDataID as nvarchar(max))

了解了这些，我们就直接上代码了：

declare @data table( FDataID int, FParentID int, FName nvarchar(255), FIsNode tinyint);insert into @data values(1,0,'销售一部',1),(2,1,'张三',0),(3,0,'销售二部',1),(14,1,'李四',0),(5,3,'王五',1);--select * from @data;with mycte as( select FDataID,FParentID,FName,1 as FLevel, cast(FName as nvarchar(max)) FLevelName, right('000000'+cast(+FDataID as nvarchar(max)),6) as FLevelID from @data where FParentID=0 union all select t1.FDataID,t1.FParentID,t1.FName,t2.FLevel+1 as FLevel, cast(replicate('.',t2.FLevel*2)+t1.FName as nvarchar(max)) FLevelName, t2.FLevelID+'.'+right('000000'+cast(t1.FDataID as nvarchar(max)),6) as FLevelID from @data t1 inner join mycte t2 on t1.FParentID=t2.FDataID)select * from mycte order by FLevelID;

运行效果参看下图：

很显然生产的结果直接就成了层次关系树型了。脚本中红框框起来，就是最核心的层级链表，查询是按照层级链表排序。生成层级链表时，为了避免排序的误差，在内码前面补零，更方便排序。

希望对您有所帮助！