declare @test table(Rownum int identity(1,1), Level int, Parent nvarchar(10), ParentDes nvarchar(100), Child nvarchar(10))insert into @test
select 1,'A','A description','a1' union all
select 2,'a1','a1 description','a2' union all
select 3,'a2','a2 description','a3' union all
select 1,'A','A description','b1' union all
select 2,'b1','b1 description','b2' union all
select 3,'b2','b2 description','b3' union all
select 1,'C','C description','c1' union all
select 2,'c1','c1 description','c2' union all
select 3,'c2','c2 description','c3' union all
select 1,'C','C description','d1' union all
select 2,'d1','d1 description','d2' union all
select 3,'d2','d2 description','d3' Rownum Level Parent ParentDes Child
1 1 A A description a1
2 2 a1 a1 description a2
3 3 a2 a2 description a3
4 1 A A description b1
5 2 b1 b1 description b2
6 3 b2 b2 description b3
7 1 C C description c1
8 2 c1 c1 description c2
9 3 c2 c2 description c3
10 1 C C description d1
11 2 d1 d1 description d2
12 3 d2 d2 description d3上述结果1到6行都是父A展开的,7到12是父C展开的,现在想把每个父项展开的所有BOM的父和父描述全部替换成最上层的数据
结果如下:
Rownum Level Parent ParentDes Child
1 1 A A description a1
2 2 A A description a2
3 3 A A description a3
4 1 A A description b1
5 2 A A description b2
6 3 A A description b3
7 1 C C description c1
8 2 C C description c2
9 3 C C description c3
10 1 C C description d1
11 2 C C description d2
12 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 description
8 2 c1 c1 description c2 C C description
9 3 c2 c2 description c3 C C description
10 1 C C description d1 C C description
11 2 d1 d1 description d2 C C description
12 3 d2 d2 description d3 C C description没有头绪呀
1 1 A A description a1
2 2 a1 a1 description a250 1 B B description a1
51 2 a1 a1 description a2两个不同的父项,刚好有一层儿子一样,层级也一样,用递归的话,就会出现重复的问题.能不能加一列,来区别每个父项展开的数据,比如
Rownum Level Parent ParentDes Child FLAG
1 1 A A description a1 P1
2 2 A A description a2 P1
3 3 A A description a3 P1
4 1 A A description b1 P1
5 2 A A description b2 P1
6 3 A A description b3 P1
7 1 C C description c1 P2
8 2 C C description c2 P2
9 3 C C description c3 P2
10 1 C C description d1 P2
11 2 C C description d2 P2
12 3 C C description d3 P2
returns nvarchar(100)
as
begin
;with cte tb
(select * from table where child=@child
union all
select * from table s inner join tb on s.child=tb.parent)
select @nm=[level] from tb where level=1
return @nm
end-------------
select * ,level=Getlevel(child)from [table]