CrazyFor (蚂蚁)
这种致富不忘穷兄弟的做法值得鼓励!
这种致富不忘穷兄弟的做法值得鼓励!
解决方案 »
- 关于这两句SQL的执行效率
- 求一SQL语句?求table哪条记录的name的字符串最长? 并且长度是多少?
- 不用临时表,如何求和!
- 评论:SQL Server ce开发怎么样?给我建议,来者有分
- 强制定阅无法连接到订阅服务器!
- 求sql server 2000 数据库恢复过程中报连接中断的解决办法
- 用存储过程从EXCEL导入不符合规范的日期型数据时出错怎么办?
- 数据库中表和列的问题!!
- 在JBuilder9中怎样使用 Statement.getGeneratedKeys
- 各位大虾,有关sqlserver7.0升级到sqlserver2000的问题!在线等候!
- 怎样判别sql 2000中某张表是否存在?在线....
- 关于update的触发
http://www.lostinet.com/temp/ItemsRelation.sql
go
insert new_tree values ( '1','2',3);
insert new_tree values ( '1','3',4);
insert new_tree values ( '2','4',3);
insert new_tree values ( '2','5',5);
insert new_tree values ( '3','6',7);
insert new_tree values ( '3','7',3);
insert new_tree values ( '3','8',2);
insert new_tree values ( '6','9',1);
insert new_tree values ( '5','10',4);
insert new_tree values ( '4','11',3);
insert new_tree values ( '9','12',7);
insert new_tree values ( '7',NULL,1);
insert new_tree values ( '8',NULL,4);
insert new_tree values ( '10',NULL,3);
insert new_tree values ( '11',NULL,3);
insert new_tree values ( '12',NULL,7);广度优先:
create proc proc_new_tree (@parent varchar(80),@mode int =0)
asbegin
set nocount on
declare @level int
declare @tmp1 table ( parent varchar(80), child varchar(80),level int)
select @level =1
insert @tmp1 select parent,child,@level from new_tree where parent = @parent
while exists(select * from @tmp1 where child is not NULL and level=@level)
begin
insert @tmp1 select a.parent,a.child ,@level+1 from new_tree a,@tmp1 b where a.parent = b.child and b.level=@level
select @level=@level +1
end
if @mode =0 select * from @tmp1
else select * from @tmp1 where child is null
set nocount off
end
go深度优先:
create proc proc__tree (@parent char(20))
as
begin
set nocount on
declare @level int ,@i int ,@flag int
declare @stack table (parent char(20),child char(20),level int,row int , flag int)
select @level = 1,@i=1,@flag=1
insert @stack select parent,child, @level,0,1 from new_tree where parent = @parent and child is not null
while @level > 0
begin
if exists (select * from @stack where level = @level and flag=1)
begin
select @parent = min(child) from @stack where level = @level and flag=1
update @stack set flag =0 , row=@i where level = @level and child = @parent and flag =1
select @i = @i +1
insert @stack select parent,child, @level + 1,0,1 from new_tree where parent = @parent and child is not null
if @@rowcount > 0
select @level = @level + 1
end
else
begin
select @level = @level - 1
end
end
select row,parent ,child,level from @stack order by row
set nocount off
end
go
proc__tree '1'
select *,dbo.fn_32gettopclass(Tc_id,default,1,default) from treeclass order by dbo.fn_32gettopclass(Tc_id,default,1,default)
/*
@Type= 0:得到顶层ID
1:得到当前到顶层的串
2:排序时使用
*/RETURNS Varchar(8000)
AS
BEGIN Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int,@OrderStr varchar(10)
if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0
BEGIN if @type=1 or @type=2
begin
if @type=2 set @OrderStr='0000000000' else set @OrderStr=''
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+right(@OrderStr+cast(@tC_ID as varchar),10)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar) if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClasswhile @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
endReturn @IdStrEND