无限分级数据表如下
---课件分类
if exists(select name from sysobjects where name='CourseCategory')
drop table CourseCategory
go
create table CourseCategory
(
AutoID int identity(1,1) not null primary key,
ParentID int not null default 0,
CateName varchar(50) not null unique,
CateDesc varchar(2000),
IsShow int null default 1,
CreateDate datetime null default getdate()
)
原理:建立一个临时表.循环存储满足条件的ID.取子分类时存储的为主键ID,取祖先ID是存储的为父ID.
----得到子ID
if exists (select name from sysobjects where name='f_getchildid')
drop function f_getchildid
go
create function f_getchildid(@node_id int)
returns @re table(id int)
as
begin
insert into @re select AutoID from CourseCategory where AutoID=@node_id
while @@rowcount>0
insert into @re select a.AutoID
from CourseCategory a inner join @re b on a.ParentID=b.id
where a.AutoID not in(select id from @re)
return
end
go
select * from t_tree
select * from dbo.f_getchildid(3)
select a.* from t_tree a join dbo.f_getchildid(2) b on a.node_id=b.id
----得到顶级ID
if exists(select name from sysobjects where name='f_getgrandid')
drop function f_getgrandid
go
create function f_getgrandid(@node_id int) returns int
begin
---@backid是返回的顶级ID
declare @backid int
declare @re table(id int)
insert into @re select AutoID from CourseCategory where AutoID=@node_id
while @@rowcount>0
begin
insert into @re
select a.ParentID from CourseCategory a inner join @re b on a.AutoID=b.id
where a.ParentID not in (select id from @re) and a.ParentID<>0
end
set @backid=(select top 1 id from @re order by id asc)
return @backid
end
select dbo.f_getgrandid(52)