1、创建函数方法
create function GetCategoryChilds(@id int) returns @category TABLE (ID int,Level int)
as
BEGIN
DECLARE @level int
set @level = 1
insert @category select @id,@level
WHILE @@ROWCOUNT>0
BEGIN
SET @level = @level+1
insert @category select c.PECFCategoryKey,@Level
FROM PECFCategory c,@category b
WHERE c.ParentKey = b.ID
AND b.Level=@Level -1
END
RETURN
END
以获取节点1下面的子节点为例子
SELECT c.*
FROM PECFCategory c,GetCategoryChilds(1) b
WHERE c.PECFCategoryKey =b.ID
2、一条SQL语句方法
SELECT * FROM PECFCategory p
WHERE p.PECFCategoryKey = 1
DECLARE @Level int
SET @Level = 1
if object_id('tempdb.dbo.#YY') is not null drop table #YY
Select *,Level = @Level into #YY from PECFCategory
Where PECFCategoryKey = 1
WHILE @@ROWCOUNT > 0
begin
set @Level = @Level +1
insert #YY select p.*, @Level
from PECFCategory p
join #YY b
ON p.ParentKey = b.PECFCategoryKey
WHEre b.Level = @Level -1
end
SELECT * FROM #YY order by 1
3、SQL server 2005或者SQL server 2008
with T as
(
SELECT * FROm PECFCategory where PECFCategoryKey = 1
union all
select a.* from PECFCategory a join T b on a.ParentKey = b.PECFCategoryKey
)
select * from T