求解SQL存储过程

[xueyou98]

--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1, 0, '中国'
union all select 2, 1, '北京'
union all select 3, 1, '上海'
union all select 4, 1, '湖北'
union all select 5, 1, '湖南'
union all select 6, 4, '武汉'
union all select 7, 4, '孝感'
union all select 8, 5, '长沙'
union all select 9, 5, '岳阳'
union all select 10, 2, '海淀区'
union all select 11, 2, '朝阳区'
union all select 12, 10, '上地'
union all select 13, 10, '西三旗'

go

--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.PID=b.ID and b.Level = @i-1
    end
    return
end
go

--执行查询
select * from dbo.f_getChild(0)
go

--输出结果
/*
ID PID Level
1 0 1
2 1 2
3 1 2
4 1 2
5 1 2
10 2 3
11 2 3
6 4 3
7 4 3
8 5 3
9 5 3
12 10 4
13 10 4
*/

--删除测试数据
drop function f_getChild
drop table BOM

以上是做测试用的，但达不到我的要求

我想实现的是这样的形式

/*
ID PID Level
1 0 1
2 1 2
10 2 3
12 10 4
13 10 4
11 2 3
3 1 2
4 1 2
6 4 3
7 4 3
5 1 2
8 5 3
9 5 3

*/

求告知存储过程或函数写法，谢谢！

[SQL77]

又是BOM,

等CTE高手来

[永生天地]

SQL Server 中树形表数据的处理总结
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx
有类似

[htl258_Tony]

--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1, 0, '中国'
union all select 2, 1, '北京'
union all select 3, 1, '上海'
union all select 4, 1, '湖北'
union all select 5, 1, '湖南'
union all select 6, 4, '武汉'
union all select 7, 4, '孝感'
union all select 8, 5, '长沙'
union all select 9, 5, '岳阳'
union all select 10, 2, '海淀区'
union all select 11, 2, '朝阳区'
union all select 12, 10, '上地'
union all select 13, 10, '西三旗'

go

--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT,px VARBINARY(8000))
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 1
    insert into @t select ID,PID,@i,CAST(ID AS VARBINARY) from BOM where PID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i ,b.px+CAST(a.id AS VARBINARY)
        from 
            BOM a,@t b 
        where 
            a.PID=b.ID and b.Level = @i-1
    end
    return
end
go

--执行查询
select ID,PID,Level from dbo.f_getChild(0) ORDER BY px
go

--输出结果
/*
ID         PID        Level
---------- ---------- -----------
1          0          1
2          1          2
10         2          3
12         10         4
13         10         4
11         2          3
3          1          2
4          1          2
6          4          3
7          4          3
5          1          2
8          5          3
9          5          3

(13 行受影响)
*/

--删除测试数据
drop function f_getChild
drop table BOM

[zl59503020]

。。。。。。没看懂。。。

[xueyou98]

非常感谢四楼，要的就是这样的算法

多谢！结贴~