set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJP]
(
@deeplevel int,
@firstcode nvarchar(100),
@liqty int,
@gd nvarchar(100)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
liqty numeric(19,8),
sh numeric(19,8),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)
insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),(WASTAGE/100),1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1
update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(liqty*(WASTAGE/100)))*QTY_NEED),sh+(WASTAGE/100),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty
from #tree
RETURN @@ERROR END
SET NOCOUNT OFF
SET XACT_ABORT OFF
这是我的一个存储过程，但是现在有个问题就是在在显示层次的时候不是根据我需要的形式显示的。
我需要显示的是
1
2
3
2
3
4
3
4
5
4
5
2
3
而它显示的层次关系是
1
2
2
3
3
3
4
4
4
5
5
高手望请教。
------解决方案--------------------
一般树节点在数据库存储，有2种方式
一种是需要递归的：id，父id
一种是无须递归的：id，层次，在本子树里的序号
你的是哪一种？
------解决方案--------------------