BOM有关问题

declare @test table(Rownum int identity(1,1), Level int, Parent nvarchar(10), ParentDes nvarchar(100), Child nvarchar(10))insert into @testselect 1,'A','A description','a1' union allselect 2,'a1','a1 description','a2' union allselect 3,'a2','a2 description','a3' union allselect 1,'A','A description','b1' union allselect 2,'b1','b1 description','b2' union allselect 3,'b2','b2 description','b3' union allselect 1,'C','C description','c1' union allselect 2,'c1','c1 description','c2' union allselect 3,'c2','c2 description','c3' union allselect 1,'C','C description','d1' union allselect 2,'d1','d1 description','d2' union allselect 3,'d2','d2 description','d3' Rownum    Level    Parent    ParentDes    Child1    1    A    A description    a12    2    a1    a1 description    a23    3    a2    a2 description    a34    1    A    A description    b15    2    b1    b1 description    b26    3    b2    b2 description    b37    1    C    C description    c18    2    c1    c1 description    c29    3    c2    c2 description    c310    1    C    C description    d111    2    d1    d1 description    d212    3    d2    d2 description    d3上述结果1到6行都是父A展开的,7到12是父C展开的,现在想把每个父项展开的所有BOM的父和父描述全部替换成最上层的数据结果如下:Rownum    Level    Parent    ParentDes    Child1    1    A    A description    a12    2    A    A description    a23    3    A    A description    a34    1    A    A description    b15    2    A    A description    b26    3    A    A description    b37    1    C    C description    c18    2    C    C description    c29    3    C    C description    c310    1    C    C description    d111    2    C    C description    d212    3    C    C description    d3或者直接加上两列也行:Rownum    Level    Parent    ParentDes    Child      Parent    ParentDes    1    1    A    A description    a1        A            A description        2    2    a1    a1 description    a2        A            A description  3    3    a2    a2 description    a3        A            A description  4    1    A    A description    b1        A            A description  5    2    b1    b1 description    b2        A            A description  6    3    b2    b2 description    b3        A            A description  7    1    C    C description    c1         C            C description8    2    c1    c1 description    c2         C            C description9    3    c2    c2 description    c3         C            C description10    1    C    C description    d1         C            C description11    2    d1    d1 description    d2         C            C description12    3    d2    d2 description    d3         C            C description没有头绪呀

create function Getlevel(@child (nvarchar(100)))returns nvarchar(100)as begin;with cte tb(select * from table where [email protected]  union all select * from table s  inner join  tb on  s.child=tb.parent)select @nm=[level] from tb where level=1return @nmend-------------select * ,level=Getlevel（child）from [table]