还是关于树的算法问题。是树结构就涉及到递归的问题。
众所周知,递归是算法中除图算法外最复杂的一种,关键是在于思想的理解!
另一方面,递归算法,堆栈调用开销相对来说很大。
最近,发现现在很多项目都涉及到这个,树型结构数据报表,树型下拉框,无限级版块分类等等
在实际开发中也很常见。
下面例子中的childs为nvarchar型,保存改目分类下的所有分类项目,写了个函数自动生成
这时,如果想知道某版块下的所有的帖子(包含子版块)
exec 'select * from t_board where boardid=@bid or boardid in (@childids)'
就可以了
--
获取所有的下级分类,末尾带,
create function [ dbo ] . [ fn_get_childids ] ( @bid int )
returns nvarchar ( 100 )
as
begin
declare @r nvarchar ( 100 )
set @r = ''
declare @tmp int
declare cur cursor for
select boardid from t_board where preid = @bid
open cur
fetch next from cur into @tmp
WHILE ( @@FETCH_STATUS <> - 1 )
BEGIN
set @r = @r + cast ( @tmp as nvarchar ( 10 )) + ' , '
set @r = @r + dbo.fn_get_childids( @tmp )
FETCH NEXT FROM cur INTO @tmp
END
close cur
deallocate cur
return ( @r )
/*
update t_board set childids=dbo.fn_get_childids(boardid)
update t_board set childids=left(childids,len(childids)-1) where childids <>''
*/
end
-- 获取所有上级分类,末尾不带,
create function [ dbo ] . [ fn_get_preids ] ( @bid int )
returns nvarchar ( 100 )
as
begin
declare @r nvarchar ( 100 )
set @r = ''
declare @pid int
set @pid = @bid
while @pid > 0
begin
select @pid = preid from t_board where boardid = @pid
-- print @pid
if ( @pid > 0 ) set @r = @r + cast ( @pid as nvarchar ) + ' , '
end
if @r <> '' set @r = left ( @r , len ( @r ) - 1 )
return ( @r )
end
create function [ dbo ] . [ fn_get_childids ] ( @bid int )
returns nvarchar ( 100 )
as
begin
declare @r nvarchar ( 100 )
set @r = ''
declare @tmp int
declare cur cursor for
select boardid from t_board where preid = @bid
open cur
fetch next from cur into @tmp
WHILE ( @@FETCH_STATUS <> - 1 )
BEGIN
set @r = @r + cast ( @tmp as nvarchar ( 10 )) + ' , '
set @r = @r + dbo.fn_get_childids( @tmp )
FETCH NEXT FROM cur INTO @tmp
END
close cur
deallocate cur
return ( @r )
/*
update t_board set childids=dbo.fn_get_childids(boardid)
update t_board set childids=left(childids,len(childids)-1) where childids <>''
*/
end
-- 获取所有上级分类,末尾不带,
create function [ dbo ] . [ fn_get_preids ] ( @bid int )
returns nvarchar ( 100 )
as
begin
declare @r nvarchar ( 100 )
set @r = ''
declare @pid int
set @pid = @bid
while @pid > 0
begin
select @pid = preid from t_board where boardid = @pid
-- print @pid
if ( @pid > 0 ) set @r = @r + cast ( @pid as nvarchar ) + ' , '
end
if @r <> '' set @r = left ( @r , len ( @r ) - 1 )
return ( @r )
end