新手请教：这段代码该如何改？

Question

if object_id('[tb]') is not null drop table [tb] 
create table [tb] (Item varchar(5),DItem varchar(9),Des varchar(5)) 
insert into [tb] 
select '001','AXA','Rec0' union all 
select '002','00201','Rec02' union all 
select '003','00301','Rec03' union all 
select 'AXA','BB0','Rec04' union all 
select 'AXA','BB1','Rec05' union all 
select '00201','0020101','Rec6' union all 
select '00201','0020102','Rec7' union all 
select 'BB0','xx1010101','Rec08' union all 
select 'BB0','yy1010102','Rec09' union all 
select 'BB1','zz1010201','Rec10' union all 
select 'BB1','uu1010202','Rec11' go 
/*
create proc sp_Test 
    @ItemID varchar(16) 
as 
begin 
    declare @layer int 
    set @layer=1 
    select *, @layer as layer into #t from tb where Item=@ItemID 
    union all select *,@layer-1 from tb where DItem=@ItemID 
    while exists(select * from tb where Item in (select DItem from #t where layer=@layer)) 
    begin        
        insert #t select *, @layer+1 
        from tb where Item in (select DItem from #t where layer=@layer) 
        set @layer=@layer+1 
    end 
    select * from #t order by layer 
end 
go */
create proc sp_Test 
    @ItemID varchar(16) 
as 
begin 
    declare @layer int 
    set @layer=1 
    select *, @layer as layer into #t from tb where Item=@ItemID 
    union all select *,@layer-1 from tb where DItem=@ItemID 
    while exists(select * from tb where Item in (select DItem from #t where layer=@layer)) 
    begin        
        insert #t select *, @layer+1 
        from tb where Item in (select DItem from #t where layer=@layer) 
        set @layer=@layer+1 
    end 
    select *,(case when layer=1 then '01' else case when layer=2 then '0102' else '010203' end end) as layPid from #t order by layer 
end 
go exec dbo.sp_Test '001' 
go
/*
Item  DItem     Des   layer       layPid
----- --------- ----- ----------- ------
001   AXA       Rec0  1           01
AXA   BB0       Rec04 2           0102
AXA   BB1       Rec05 2           0102
BB0   xx1010101 Rec08 3           010203
BB0   yy1010102 Rec09 3           010203
BB1   zz1010201 Rec10 3           010203
BB1   uu1010202 Rec11 3           010203(7 行受影响)
*/

Answer 1

    select *,(case layer when 1 then '01' when 2 then '0102' when 3 then '010203'end) as layPid from #t order by layer 

Answer 2

那如果LAYER是3层甚至更多的应该怎么办？

Answer 3

SQL05用CTE:
--> 测试数据: [tb] 
if object_id('[tb]') is not null drop table [tb] 
create table [tb] (Item varchar(5),DItem varchar(9),Des varchar(5)) 
insert into [tb] 
select '001','AXA','Rec0' union all 
select '002','00201','Rec02' union all 
select '003','00301','Rec03' union all 
select 'AXA','BB0','Rec04' union all 
select 'AXA','BB1','Rec05' union all 
select '00201','0020101','Rec6' union all 
select '00201','0020102','Rec7' union all 
select 'BB0','xx1010101','Rec08' union all 
select 'BB0','yy1010102','Rec09' union all 
select 'BB1','zz1010201','Rec10' union all 
select 'BB1','uu1010202','Rec11' go declare @ItemID varchar(16) 
set @ItemID='001' 
;with tree as 
(select *,layer=1,layPid=cast('01' as nvarchar(50)) from [tb] where Item=@ItemID
union all
select b.*,a.layer+1,cast(a.layPid+right(101+a.layer,2) as nvarchar(50)) from tree a join [tb] b on a.DItem=b.Item )
select * from Tree
Item  DItem     Des   layer       layPid
----- --------- ----- ----------- --------------------------------------------------
001   AXA       Rec0  1           01
AXA   BB0       Rec04 2           0102
AXA   BB1       Rec05 2           0102
BB1   zz1010201 Rec10 3           010203
BB1   uu1010202 Rec11 3           010203
BB0   xx1010101 Rec08 3           010203
BB0   yy1010102 Rec09 3           010203(7 行受影响)

Answer 4

忘记告诉各位高手了，我用的SQL server是2000的版本，不支持
declare @ItemID varchar(16) 
set @ItemID='001' 
;with tree as 
(select *,layer=1,layPid=cast('01' as nvarchar(50)) from [tb] where Item=@ItemID
union all
select b.*,a.layer+1,cast(a.layPid+right(101+a.layer,2) as nvarchar(50)) from tree a join [tb] b on a.DItem=b.Item )
select * from Tree
的执行，请各位大侠再看看

Answer 5

 
alter  proc sp_Test 
    @ItemID varchar(16) 
as 
begin 
    declare @m varchar(20)
    declare @t int 
    set @t=1
    set @m=''
    declare @layer int 
    set @layer=1     select *, @layer as layer,cast('01' as varchar(20)) as layerID into #t from tb where Item=@ItemID 
    union all select *,@layer-1,'01' from tb where DItem=@ItemID 
    while exists(select * from tb where Item in (select DItem from #t where layer=@layer)) 
    begin  
---增加一个循环，如果觉得代码冗余、麻烦，最好写一个函数调用
       while @t<=@layer+1
        begin
             set @m=@m+'0'+cast(@t as varchar(10))
             set @t=@t+1
        end 
---------------------------------------------
        insert #t select *,@layer+1,+@m
        from tb where Item in (select DItem from #t where layer=@layer) 
        set @layer=@layer+1 
        set @t=1
        set @m=''
    end   select *from #t end 
go 看可以吗?
虽然麻烦点

Answer 6

--> 测试数据: [tb] 
if object_id('[tb]') is not null drop table [tb] 
create table [tb] (Item varchar(20),DItem varchar(9),Des varchar(5)) 
insert into [tb] 
select '001','AXA','Rec0' union all 
select '002','00201','Rec02' union all 
select '003','00301','Rec03' union all 
select 'AXA','BB0','Rec04' union all 
select 'AXA','BB1','Rec05' union all 
select '00201','0020101','Rec6' union all 
select '00201','0020102','Rec7' union all 
select 'BB0','xx1010101','Rec08' union all 
select 'BB0','yy1010102','Rec09' union all 
select 'BB1','zz1010201','Rec10' union all 
select 'BB1','uu1010202','Rec11' union all
select 'zz1010201','aa','Rec10' union all 
select 'zz1010201','bb','Rec11' 
go exec dbo.sp_Test '001'
/*
结果
AXA BB0 Rec04   2 0102
AXA BB1        Rec05 2 0102
BB0 xx1010101 Rec08 3 010203
BB0 yy1010102 Rec09 3 010203
BB1 zz1010201 Rec10 3 010203
BB1 uu1010202 Rec11 3 010203
zz1010201 aa Rec10 4 01020304
zz1010201 bb Rec11 4 01020304
001 AXA         Rec0 1 01
*/ 

Answer 7

 select *from #t  order by layerID 
忘记排序了
追加