SQL 无限极分类获取子节点分类的SQL

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