无限级分类查询子类SQL

create table #tb(类别id  int,类别名称 varchar(40),上级类别id int)
insert #tb select  1 ,'顶级类别', 0
insert #tb select  2 ,'类别1', 1
insert #tb select  3 ,'类别2', 1
insert #tb select  4 ,'类别1_1', 2
;with cte as 
(
    select 类别id,类别名称,上级类别id,CAST(row_number() over(order by 类别id) as varbinary(max)) as sort
    from #tb
    where 上级类别id = 1
    
    union all
    
    select a.类别id,a.类别名称,a.上级类别id,sort+CAST(row_number() over(partition by a.上级类别id order by a.类别id) as BINARY)
    from  #tb a
    inner join cte b
    ON a.上级类别id = b.类别id 
)
select 类别id,类别名称,上级类别id
from  cte 
order by sort

类别id        类别名称                                     上级类别id
----------- ---------------------------------------- -----------
2           类别1                                      1
4           类别1_1                                    2
3           类别2                                      1
(3 行受影响)

--drop table #tb
--create table #tb(CategoryId  int,CategoryName nvarchar(500),ParentCategoryId int,Featured bit,ChildCount int)
/*
insert #tb select  1 ,'顶级类别', 0
insert #tb select  2 ,'类别1', 1
insert #tb select  3 ,'类别2', 1
insert #tb select  4 ,'类别1_1', 2
*/
alter function [dbo].[GetChilds](@id int)
returns @Tree table (CategoryId int,CategoryName nvarchar(500),ParentCategoryId int,DisplayOrder int,Featured bit,ChildCount int)
as
begin
insert @Tree select CategoryID,CategoryName,ParentCategoryId,DisplayOrder,Featured,(select COUNT(1) from Category p where p.ParentCategoryId=@id) as ChildCount from Category where CategoryID=@id
;with cte as 
(
    select CategoryId,CategoryName,ParentCategoryId,DisplayOrder,Featured,CAST(row_number() over(order by CategoryId) as varbinary(max)) as sort
    from Category
    where ParentCategoryId = @id
    
    union all
    
    select a.CategoryId,a.CategoryName,a.ParentCategoryId,a.DisplayOrder,a.Featured,sort+CAST(row_number() over(partition by a.ParentCategoryId order by a.CategoryId) as BINARY)
    from  Category a
    inner join cte b
    ON a.ParentCategoryId = b.CategoryId 
)
insert @Tree 
select CategoryId,CategoryName,ParentCategoryId,DisplayOrder,Featured,(select COUNT(1) from cte q where q.ParentCategoryId=cte.CategoryId) as ChildCount
from  cte 
--order by ParentCategoryId,DisplayOrder,CategoryId,sort

return
end
--select * from #tb