請問量“提取樹結構數據的SQL語句”怎麼寫(200分)

  • 主题发起人 主题发起人 无厘头
  • 开始时间 开始时间

无厘头

Unregistered / Unconfirmed
GUEST, unregistred user!
數據庫為MS SQL Server 2000<br>表結構是這樣的<br>ID, Name, PID<br>------------------<br>0 &nbsp; A &nbsp; &nbsp; p0<br>1 &nbsp; B &nbsp; &nbsp; 0<br>2 &nbsp; C &nbsp; &nbsp; 1<br>3 &nbsp; D &nbsp; &nbsp; 2<br>4 &nbsp; E &nbsp; &nbsp; P1<br>5 &nbsp; F &nbsp; &nbsp; 4<br>我想跟據PID=p0就能取出該樹結構的所有數據<br>ID, Name, PID<br>------------------<br>0 &nbsp; A &nbsp; &nbsp; p0<br>1 &nbsp; B &nbsp; &nbsp; 0<br>2 &nbsp; C &nbsp; &nbsp; 1<br>3 &nbsp; D &nbsp; &nbsp; 2
 
有深度有意义的问题,等我想下把代码贴出来
 
(1) 一般的树设计都必须具备以下字段:ParentID, ChildID, Description, PID, CID, RecID。字段说明如下:<br><br> ParentID 父节点编码 varchar(20)<br> ChildID 当前节点编码 varchar(20)。理论上要唯一,但可修改,所以不能作为关键字。<br> Description 当前节点描述 varchar(80)<br> PID 父节点的后台ID int<br> CID 当前节点后台ID int。关键字,唯一。<br> RecID 自增长标识,步进为1<br> <br> &nbsp; &nbsp;例如:<br> 根节点<br> &nbsp; |___ PP_AAA<br> &nbsp; &nbsp; |___ PS_AAA<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; |___ PS_A001<br> &nbsp; | &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; |___ PS_A111<br> &nbsp; | &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |___ ... (叶节点可以无限扩展) <br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; |___ PS_A002<br> &nbsp; |___ PS_BBB<br> |___ PS_B001<br><br> &nbsp; &nbsp;在表中存储如下:<br> ParenetID ChildID Description PID CID RecID<br> ----------------------------------------------------------------------<br> -1 Root 根节点 -1 0 1<br> Root PP_AAA PP_AAA 0 1 2<br> PP_AAA PS_AAA PS_AAA 1 2 3<br> PS_AAA PS_A001 PS_A001 2 3 4<br> PS_A001 PS_A111 PS_A111 3 4 5<br> PS_AAA PS_A002 PS_A002 2 5 6<br> PP_AAA PS_BBB PS_BBB 1 6 7<br> PS_BBB PS_B001 PS_B001 6 7 8<br><br>使用逐层查找法。例如我们要查找“PP_AAA”,即可如下办法:<br> <br>(1)创建一个正式表t_MyTree,存储当前节点,保存为第一层。如:<br> ParenetID ChildID CID TreeLevel(第几层)<br> ----------------------------------------------------<br> ??? PP_AAA 1 1<br> &nbsp; 用变量@End记住该层有 1 个节点。<br><br>(2)寻找第一层所有下级子节点,若找到,把他们写入表t_MyTree,保存为第二层。<br> ParenetID ChildID CID TreeLevel(第几层)<br> ----------------------------------------------------<br> ??? PP_AAA 1 1<br> PP_AAA PS_AAA 2 2<br> PP_AAA PS_BBB 6 2<br> &nbsp; 用变量@End记住该层有 2 个节点。<br><br>(3)利用游标,逐个寻找第二层各节点的下级子节点,若找到,把他们写入表t_MyTree,保存为第三层。<br> 对第二层第一个节点PS_AAA寻找后,可得如下数据:<br> ParenetID ChildID TreeLevel(第几层)<br> ----------------------------------------------------<br> ??? PP_AAA 1 1<br> PP_AAA PS_AAA 2 2<br> PP_AAA PS_BBB 6 2<br> PS_AAA PS_A001 3 3<br> PS_AAA PS_A002 5 3<br><br> 对第二层第二个节点PS_BBB寻找后,可得如下数据:<br> ParenetID ChildID CID TreeLevel(第几层)<br> ----------------------------------------------------<br> ??? PP_AAA 1 1<br> PP_AAA PS_AAA 2 2<br> PP_AAA PS_BBB 6 2<br> PS_AAA PS_A001 3 3<br> PS_AAA PS_A002 5 3<br> PS_BBB PS_B001 7 3 <br> &nbsp; 用变量@End记住该层有 3 个节点。<br> <br>(4)循环(3),逐个寻找第三层各节点的下级子节点,若找到,把他们写入表t_MyTree,保存为第四层。<br> 对第三层的第一个节点PS_A001寻找后,可得如下数据:<br> ParenetID ChildID CID TreeLevel(第几层)<br> ----------------------------------------------------<br> ??? PP_AAA 1 1<br> PP_AAA PS_AAA 2 2<br> PP_AAA PS_BBB 6 2<br> PS_AAA PS_A001 3 3<br> PS_AAA PS_A002 5 3<br> PS_BBB PS_B001 7 3<br> PS_A001 PS_A111 4 4<br><br> 对第三层的第二个、第三个节点寻找都找不到下级子节点。<br> &nbsp; 用变量@End记住该层有 1 个节点。<br><br>(5)循环(3),逐个寻找第四层各节点的下级子节点,但找不到。<br> &nbsp; 用变量@End记住该层有 0 个节点。<br><br>至此,当变量 @End 等于 0 时,所有节点寻找完毕!把表t_MyTree按 CID 升序排序,即得到我们要的结果。<br>实际上,我们采用 PID 和 CID 运算会比用 ParentID 和 ChildID 好,这里之所以不用 PID 和 CID,是因为两者数据不如ParentID和ChildID直观,不便于说明问题,实际应用时请使用PID和CID。<br><br><br>-- 1、按逐层查找法得到树指定节点的所有下级子节点<br><br>Create Proc sp_GetTreeNodeByStep<br> @SrcChildID varchar(20)<br>As<br><br>-- 声明输出表<br>declare @Tree table (<br> ParentID varchar(20),<br> ChildID varchar(20),<br> PID int,<br> CID int,<br> TreeLevel int<br>)<br><br>-- 声明变量<br>Declare @ParentID varchar(20),<br> @ChildID varchar(20),<br> @TreeLevel int,<br> @End int<br><br><br>-- 存储当前节点<br>Insert Into @Tree(ParentID, ChildID, TreeLevel)<br>Values('???', @SrcChildID, 1)<br><br>-- 初始化变量<br>Set @TreeLevel = 1<br>Set @End = 1<br><br>While @End &lt;&gt; 0<br>begin<br> -- 逐个寻找第 @TreeLevel 层各节点的下级子节点<br> Declare cur Cursor for<br> Select ParentID, ChildID from @Tree where TreeLevel = @TreeLevel<br> Open cur<br> Fetch From cur Into @ParentID, @ChildID<br> While @@Fetch_Status = 0<br> begin<br> -- 寻找第 @TreeLevel 层下 @ChildID 节点的下级子节点,若存在,写入输出表,保存为第 @TreeLevel + 1 层<br> if Exists(Select * from t_TCH_PrdtTree where ParentID = @ChildID)<br> begin<br> Insert @Tree(ParentID, ChildID, TreeLevel)<br> Select ParentID, ChildID, @TreeLevel + 1<br> From t_TCH_PrdtTree where ParentID = @ChildID<br> end<br> Fetch From cur Into @ParentID, @ChildID<br> end<br> Close cur<br> DealLocate cur<br><br> -- 记住下一层有多少个节点<br> Select @End = Count(*) from @Tree where TreeLevel = @TreeLevel + 1<br><br> -- 第 @TreeLevel 层寻找完毕,准备查找下一层<br> Set @TreeLevel = @TreeLevel + 1<br>end<br><br>Update t<br>Set t.PID = p.PID, t.CID = p.CID<br>From @Tree t, t_TCH_PrdtTree p<br>where t.ChildID = p.ChildID And t.ParentID = p.ParentID<br><br>Select * from @Tree <br>Order by CID
 
Create procedure SelectTree<br> @SelectedID int --传入一个PID<br><br>as<br><br>begin<br> declare @HasChild bit, --用来指示是否还有子节点<br> @TmpID int,<br> @TmpName varchar(10),<br> @TmpPID int,<br> @TmpCondition varchar(100)<br><br> declare @TmpTree table (<br> ID int,<br> Name varchar(10),<br> PID int<br> )<br> set @HasChild = 1<br><br> set @TmpCondition = @SelectedID<br><br> while (@HasChild = 1)<br> begin<br><br> declare TmpCursor cursor for <br> select * from TreeTbl where PID in (@TmpCondition)<br> <br> open TmpCursor<br> fetch next from TmpCursor into @TmpID,@TmpName,@TmpPID<br> <br><br> if @@FETCH_STATUS &lt;&gt; 0 <br> set @HasChild = 0<br><br> set @TmpCondition = @TmpID<br> <br> while(@@FETCH_STATUS = 0)<br> begin<br> insert into @TmpTree values(@TmpID,@TmpName,@TmpPID)<br> fetch next from TmpCursor into @TmpID,@TmpName,@TmpPID<br> if @@FETCH_STATUS = 0<br> set @TmpCondition = @TmpCondition + ',' + convert(varchar(10),@TmpID)<br> end<br><br> close TmpCursor<br> deallocate TmpCursor<br> end<br><br><br> select * from @TmpTree<br>end
 
多人接受答案了。
 
有没有搞错啊,我可是真正辛苦写了代码的,楼主你拿到查询分析器运行一下就知道了,才给我60分
 
后退
顶部