- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[P_DJP](@deeplevel int,@firstcode nvarchar(100),@liqty int,@gd nvarchar(100))ASSET NOCOUNT ONSET XACT_ABORT ONBEGINcreate 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 [email protected] WHILE @@rowcount > 0 BEGIN SET @deeplevel = @deeplevel + 1 update #tree set isLeafnode= 0 from #tree join BOMT on [email protected] 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 [email protected] and BOMT.BOMNO collate database_default =#tree.code left join MAINBOM on BOMT.CODE=MAINBOM.BOMNOENDselect space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty from #tree RETURN @@ERROR ENDSET NOCOUNT OFFSET 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，层次，在本子树里的序号
你的是哪一种？
------解决方案--------------------
我记得上次帮你写过。。
http://topic.csdn.net/u/20110506/16/572420d1-3bc8-4e51-8aef-d29715fe09a8.html
------解决方案--------------------
參考,使用全路徑排序：
- SQL code
use tempdbGoif object_id('#') Is Not Null Drop Table #Create Table #( ID int , ParentID int, level smallint, value nvarchar(10)) Goinsert into #(ID,ParentID,level,value) Select 1,0,1,'Root' Union All Select 2,1,2,'A1' Union All Select 3,1,2,'B1' Union All Select 4,1,2,'C1' Union All Select 5,2,3,'A11' Union All Select 6,3,3,'B11' Union All Select 7,5,4,'A111' Union All Select 8,6,4,'B111' Union All Select 9,8,5,'B1111'Go;With CTE_Seq As( Select ID,ParentID,level,value,convert(nvarchar(200),rtrim(id)) As IDPath From # Where ParentID=0 Union All Select b.ID,b.ParentID,b.level,b.value,convert(nvarchar(200),a.IDPath+'.'+rtrim(b.id)) As IDPath From CTE_Seq As a Inner Join # As b On b.ParentID=a.ID )Select value,level,IDPath From CTE_Seq Order By IDPath/*value level IDPath---------- ------ ----------Root 1 1A1 2 1.2A11 3 1.2.5A111 4 1.2.5.7B1 2 1.3B11 3 1.3.6B111 4 1.3.6.8B1111 5 1.3.6.8.9C1 2 1.4*/