--测试: create table 表(id int identity(1,1),parent_name char(1),partname char(1),level int) insert 表 values('A','B',1) insert 表 values('A','C',1) insert 表 values('A','D',1) insert 表 values('B','E',2) insert 表 values('B','F',2) insert 表 values('C','H',2) insert 表 values('E','G',3) go create function bom (@id char(1)) returns @tb table (partname char(1),parent_name char(1),level int,top1 varchar(20)) as begin insert @tb select partname,parent_name,level,@id+partname from 表 where parent_name = @id while @@rowcount > 0 insert @tb select partname,parent_name,level, (select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a where parent_name in (select partname from @tb) and partname not in (select partname from @tb) return end go select parent_name,partname,level from dbo.bom('A') order by top1/* parent_name partname level ----------- -------- ----------- A B 1 B E 2 E G 3 B F 2 A C 1 C H 2 A D 1(所影响的行数为 7 行) */
--这样处理 declare @tor table(partname varchar(10),aa varchar(8000)) declare @level int set @level=1insert into @tor select partname,partname from 表 where level=@level while @@rowcount>0 begin set @level=@level+1 insert into @tor select a.partname,b.aa+','+a.partname from 表 a join @tor b on a.parent_name=b.partname where a.level=@level end--查询得到结果: select a.* from 表 a join @tor b on a.partname=b.partname order by b.aa
要加上ID的话如下调用:select (select sum(1) from dbo.bom('A') where top1<=a.top1) as id, parent_name,partname,level from dbo.bom('A') a order by top1id parent_name partname level ----------- ----------- -------- ----------- 1 A B 1 2 B E 2 3 E G 3 4 B F 2 5 A C 1 6 C H 2 7 A D 1(所影响的行数为 7 行)
--测试数据 declare @t table(id int,parent_name varchar(10),partname varchar(10),Level int) insert into @t select 1,'A','B',1 union all select 2,'A','C',1 union all select 3,'A','D',1 union all select 4,'B','E',2 union all select 5,'B','F',2 union all select 6,'C','H',2 union all select 7,'E','G',3--排序处理 declare @tor table(partname varchar(10),aa varchar(8000)) declare @level int set @level=1insert into @tor select partname,partname from @t where level=@level while @@rowcount>0 begin set @level=@level+1 insert into @tor select a.partname,b.aa+','+a.partname from @t a join @tor b on a.parent_name=b.partname where a.level=@level end--查询得到结果 select a.* from @t a join @tor b on a.partname=b.partname order by b.aa/*--测试结果 id parent_name partname Level ----------- ----------- ---------- ----------- 1 A B 1 4 B E 2 7 E G 3 5 B F 2 2 A C 1 6 C H 2 3 A D 1(所影响的行数为 7 行) --*/
create table 表(id int identity(1,1),parent_name char(1),partname char(1),level int)
insert 表 values('A','B',1)
insert 表 values('A','C',1)
insert 表 values('A','D',1)
insert 表 values('B','E',2)
insert 表 values('B','F',2)
insert 表 values('C','H',2)
insert 表 values('E','G',3)
go
create function bom (@id char(1))
returns @tb table (partname char(1),parent_name char(1),level int,top1 varchar(20))
as
begin
insert @tb select partname,parent_name,level,@id+partname from 表 where parent_name = @id
while @@rowcount > 0
insert @tb select partname,parent_name,level,
(select top1 from @tb where partname = a.parent_name)+parent_name+partname from 表 a
where parent_name in (select partname from @tb)
and partname not in (select partname from @tb)
return
end
go
select parent_name,partname,level from dbo.bom('A')
order by top1/*
parent_name partname level
----------- -------- -----------
A B 1
B E 2
E G 3
B F 2
A C 1
C H 2
A D 1(所影响的行数为 7 行)
*/
declare @tor table(partname varchar(10),aa varchar(8000))
declare @level int
set @level=1insert into @tor select partname,partname from 表 where level=@level
while @@rowcount>0
begin
set @level=@level+1
insert into @tor select a.partname,b.aa+','+a.partname
from 表 a join @tor b on a.parent_name=b.partname
where a.level=@level
end--查询得到结果:
select a.*
from 表 a join @tor b on a.partname=b.partname
order by b.aa
order by top1id parent_name partname level
----------- ----------- -------- -----------
1 A B 1
2 B E 2
3 E G 3
4 B F 2
5 A C 1
6 C H 2
7 A D 1(所影响的行数为 7 行)
declare @t table(id int,parent_name varchar(10),partname varchar(10),Level int)
insert into @t
select 1,'A','B',1
union all select 2,'A','C',1
union all select 3,'A','D',1
union all select 4,'B','E',2
union all select 5,'B','F',2
union all select 6,'C','H',2
union all select 7,'E','G',3--排序处理
declare @tor table(partname varchar(10),aa varchar(8000))
declare @level int
set @level=1insert into @tor select partname,partname from @t where level=@level
while @@rowcount>0
begin
set @level=@level+1
insert into @tor select a.partname,b.aa+','+a.partname
from @t a join @tor b on a.parent_name=b.partname
where a.level=@level
end--查询得到结果
select a.*
from @t a join @tor b on a.partname=b.partname
order by b.aa/*--测试结果
id parent_name partname Level
----------- ----------- ---------- -----------
1 A B 1
4 B E 2
7 E G 3
5 B F 2
2 A C 1
6 C H 2
3 A D 1(所影响的行数为 7 行)
--*/
更多的参考我的贴子:树形数据的处理
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.7908594