SQL_存储过程CET递归查询获得树的叶子节点

USE [SooilSemanticsDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_Semantics_GetLeaf]    Script Date: 2015/6/24 17:37:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_Semantics_GetLeaf]
(
	@Parent nvarchar(255),
	@SR varchar(20)
)
as
begin
	with Childs(ConceptClassId,GlossaryId)
	as 
	(
		select s.CC2,s.T2
		from [dbo].[Semantics] s
			inner join [dbo].[CCTerm] cc on s.CC1 = cc.[ConceptClassId] and s.T1 = cc.[GlossaryId]
			inner join [dbo].[Glossary] g on s.T1 = g.Id
		where g.Term = @Parent
		  and s.SR = @SR

		union all

		select s.CC2,s.T2
		from Childs c
			inner join [dbo].[Semantics] s on c.ConceptClassId = s.CC1 and c.GlossaryId = s.T1
		where s.SR = @SR
		
	)

	select class.Tag
		  ,class.[Type]
		  ,g.Term
	from Childs child
		inner join [dbo].[ConceptClass] class on child.ConceptClassId = class.Id
		inner join [dbo].[Glossary] g on child.GlossaryId = g.Id
		left join [dbo].[Semantics] s on child.ConceptClassId = s.CC1 and child.GlossaryId = s.T1 and s.SR = @SR
	where s.T1 is null

	--select class.Tag
	--	  ,class.[Type]
	--	  ,g2.Term
	--from [dbo].[Semantics] s1
	--	inner join [dbo].[CCTerm] cc1 on s1.CC1 = cc1.[ConceptClassId] and s1.T1 = cc1.[GlossaryId]
	--	inner join [dbo].[Glossary] g1 on s1.T1 = g1.Id
	--	inner join [dbo].[CCTerm] cc2 on s1.CC2 = cc2.[ConceptClassId] and s1.T2 = cc2.[GlossaryId]
	--	inner join [dbo].[Glossary] g2 on s1.T2 = g2.Id
	--	inner join [dbo].[ConceptClass] class on s1.CC2 = class.Id
	--	left join [dbo].[Semantics] s2 on s1.CC2 = s2.CC1 and s1.T2=s2.T1 and s1.SR = s2.SR
	--where g1.Term = @Parent
	--  and s1.SR = @SR
	--  and s2.T1 is not null
end

SQL CET 递归查询

简单示例

create table Tree  
(  
  ID int identity(1,1) primary key not null,  
  Name varchar(20) not null,  
  Parent varchar(20) null  
)  
go  
insert Tree values('大学',null)  
insert Tree values('学院','大学')  
insert Tree values('计算机学院','学院')  
insert Tree values('网络工程','计算机学院')  
insert Tree values('信息管理','计算机学院')  
insert Tree values('电信学院','学院')  
insert Tree values('教务处','大学')  
insert Tree values('材料科','教务处')  
insert Tree values('招生办','大学')  
go  
with CTE as  
(  
-->Begin 一个定位点成员  
 select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null  
-->End   
union all  
-->Begin一个递归成员  
 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle  
        from Tree inner join CTE  
        on Tree.Parent=CTE.Name  
-->End  
)  
select * from CTE order by ID  

1. --1.将 CTE 表达式拆分为定位点成员和递归成员。  
2. --2.运行定位点成员，创建第一个调用或基准结果集 (T0)。  
3. --3.运行递归成员，将 Ti 作为输入(这里只有一条记录)，将 Ti+1 作为输出。  
4. --4.重复步骤 3，直到返回空集。  
5. --5.返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

另一种修改后的查询方法

with CTE as    
(     
-->Begin 一个定位点成员     
 select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,  
        ROW_NUMBER()over(order by getdate()) as OrderID  
        --最关键是上面这个字段，要获取排序字段，按字符串来排序。  
        --其中窗口函数必须要使用order by，但是不能用整型，那就用时间吧  
        from Tree where Parent is null    
-->End      
union all     
-->Begin一个递归成员     
 select Tree.ID, Tree.Name,Tree.Parent,cast(<span style="color:#ff0000;">replicate</span>(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,  
        CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID  
        from Tree inner join CTE     
        on Tree.Parent=CTE.Name     
-->End  <span style="color:#ff0000;"><span style="font-family: Arial, Helvetica, sans-serif;">replicate  </span><span style="font-family: Arial; font-size: 13px; line-height: 20px;">将字串值重复指定的次数</span>  </span>
)     
select * from CTE  
order by LTRIM(OrderID)--最后将这个整型数据转换为字符串型的进行排序  

1. --有时候整型可以比大小，字符串也可以，字符串比的大小是一位一位进行字符比较的  
2. --整型+字符串==整型，只有字符串+字符串==两个字符串的并和  
3. --递归查询中：第二条记录可以引用第一条记录的值  
4. --动态加载记录时，同一个等级的记录识别符：RowNumber()over(order by getdate())  
5. --延伸：可以动态获取某个部门下的所以子部门。也可以获取该部门上级的所以部门  
6.   
7. --总结：首先要拼凑出一个整型数据，然后转换为字符串，最后是进行字符串的order，而不是整型数据的order，

注解：

CAST 和 CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
 
语法
使用 CAST：
 
CAST ( expression AS data_type ) 
 
使用 CONVERT：
 
CONVERT (data_type[(length)], expression [, style])
 
参数
expression
 
是任何有效的 Microsoft® SQL Server™ 表达式。有关更多信息，请参见表达式。 
 
data_type
 
目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。 
 
length
 
nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。