如果仅仅是要求子结点总是在父结点的后面,但不必相临,下面的代码应该可以作到。我没有运行它,如果你能看明白,相信你能把它调试通过。create table #tmptable0 (OrderNo int, NodeID int, ParentNodeID int) create table #tmptable1 (OrderNo int, NodeID int)declare @PID int, @ONo int select @PID=-1, @ONo=0while (true) begin insert into #tmptable0 (OrderNo, NodeID, ParentNodeID) select @ONo, NodeID, ParentNodeID from tablename where ParentNodeID=@PID
insert into #tmptable1 (OrderNo,NodeID) select @ONo, NodeID from tablename where ParentNodeID=@PID if @PID=-1 delete from #tmptable1 where NodeID=-1
select top 1 @ONo=OrderNo+1, @PID=NodeID from #tmptable1 if @@RowCount=0 break delete from #tmptable1 where NodeID=@PIDenddrop #tmptable1-- 用下面的语句可以得到顺序:子结点总是在父结点的后面。 select a.NodeID, a.ParentNodeID, b.DATA from #tmptable0 as a join tablename as b on a.NodeID=b.NodeID order by a.OrderNo
下面这样简单。呵呵create table #tmptable (OrderNo int, NodeID int)declare @NodeLevel int select @NodeLevel=1insert into #tmptable (OrderNo, NodeID) select @NodeLevel, NodeID from tablename where ParentNodeID=-1update #tmptable set @NodeLevel=0 where NodeID=-1while (true) begin insert into #tmptable (OrderNo, NodeID) select @NodeLevel+1, b.NodeID from tablename as a, #tmptable as b where a.NodeID = b.ParentNodeID and b.OrderNo = @NodeLevel if @@Rowcount==0 break select @NodeLevel = @NodeLevel + 1end
后面的order by 就是排序了。
create table #tmptable1 (OrderNo int, NodeID int)declare @PID int, @ONo int
select @PID=-1, @ONo=0while (true)
begin insert into #tmptable0 (OrderNo, NodeID, ParentNodeID)
select @ONo, NodeID, ParentNodeID from tablename where ParentNodeID=@PID
insert into #tmptable1 (OrderNo,NodeID)
select @ONo, NodeID from tablename where ParentNodeID=@PID if @PID=-1
delete from #tmptable1 where NodeID=-1
select top 1 @ONo=OrderNo+1, @PID=NodeID from #tmptable1 if @@RowCount=0 break delete from #tmptable1 where NodeID=@PIDenddrop #tmptable1-- 用下面的语句可以得到顺序:子结点总是在父结点的后面。
select a.NodeID, a.ParentNodeID, b.DATA
from #tmptable0 as a join tablename as b on a.NodeID=b.NodeID
order by a.OrderNo
==>
drop tbale #tmptable1
显然你使用的方法是正确的,但是我想应该有比这更简单的方法?
1 NULL 1
2 NULL 1
3 1 2
4 1 2
5 3 3
......
.....
select @NodeLevel=1insert into #tmptable (OrderNo, NodeID)
select @NodeLevel, NodeID from tablename where ParentNodeID=-1update #tmptable set @NodeLevel=0 where NodeID=-1while (true)
begin insert into #tmptable (OrderNo, NodeID)
select @NodeLevel+1, b.NodeID
from tablename as a, #tmptable as b
where a.NodeID = b.ParentNodeID and b.OrderNo = @NodeLevel if @@Rowcount==0
break select @NodeLevel = @NodeLevel + 1end
Thank you