类似生产企业BOM材料单多层结构显示成单层结构
Stock Component
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
1 9
1 10
2 11
2 12结果
Stock Component
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
A 9
A 10
A 11
A 12列子是三层的结构,实际可能会有N未知个数层
向高手请教,好像要用到递归
Stock Component
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
1 9
1 10
2 11
2 12结果
Stock Component
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
A 9
A 10
A 11
A 12列子是三层的结构,实际可能会有N未知个数层
向高手请教,好像要用到递归
insert t select 'A', 1
insert t select 'A', 2
insert t select 'A', 3
insert t select 'A', 4
insert t select 'A', 5
insert t select 'A', 6
insert t select 'A', 7
insert t select 'A', 8
insert t select '1', 9
insert t select '1', 10
insert t select '2', 11
insert t select '2', 12
go
create function test_f(@Stock nvarchar(2))
returns nvarchar(2)
as
begin while exists(select 1 from t where Component=@Stock )
begin
select @Stock=Stock from t where Component=@Stock
end
return @Stock
endgo
select dbo.test_f(Stock)as Stock,Component from t
Stock Component
----- ---------
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
A 9
A 10
A 11
A 12(所影响的行数为 12 行)
drop function test_f
drop table t
例子如下:
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx