今天做项目刚好需要一个获得每个节点的levelno,就想起了sql2005的CTE表达式,试用了一下,效果不错.
我的理解CTE是可以复用的虚拟表,它可以实现自递归.
参考文章:Sql Server2005 Transact-SQL 新兵器学习总结之-公用表表达式(CTE)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
WITH BaseFundsProject_CTE ( Id, Code, ParentId, [Name], LevelNo, YearNo, ShowOrder, [Description], IsActiveFlag )
AS ( SELECT Id ,
Code ,
ParentId ,
[Name] ,
1 AS LevelNo ,
YearNo ,
ShowOrder ,
[Description] ,
IsActiveFlag
FROM BaseFundsProject
WHERE ISNULL(ParentId, '') = ''
AND YearNo = @YearNo
UNION ALL
SELECT c.Id ,
c.Code ,
c.ParentId ,
c.[Name] ,
LevelNo + 1 ,
C.YearNo ,
C.ShowOrder ,
C.[Description] ,
C.IsActiveFlag
FROM BaseFundsProject c
INNER JOIN BaseFundsProject_CTE ct ON c.ParentId = ct.Code
AND C.YearNo = @YearNo
)
SELECT Id ,
Code ,
ParentId ,
[Name] ,
LevelNo ,
YearNo ,
ShowOrder ,
[Description] ,
IsActiveFlag
FROM BaseFundsProject_CTE
ORDER BY Code