举例:--建立环境 create Table #BOM(ParentItem varchar(10), ChildItem varchar(10)) insert #bom values('A','B') insert #bom values('A','C') insert #bom values('A','D') insert #bom values('A','E') insert #bom values('B','F') insert #bom values('B','G') insert #bom values('D','H') insert #bom values('D','I') insert #bom values('F','J') insert #bom values('F','K')--临时表 select * into #xxx from #bom--深度 declare @tmp1 table (a varchar(10),b varchar(10),c int,d int) --存树放顺序,及子树号 declare @tmp2 table (a varchar(10),b varchar(10),c int) --存放层次declare @i int,@j varchar(100) set @j=1while exists (select 1 from #xxx) begin set @i=1 insert @tmp1 select top 1 *,@i,@j from #xxx while exists(select 1 from #xxx a,@tmp1 b where a.parentitem=b.b and a.childitem not in (select b from @tmp1)) begin insert @tmp1 select top 1 a.*,@i+1,@j from #xxx a,@tmp1 b where a.parentitem=b.b and a.childitem not in (select b from @tmp1) order by a.parentitem desc set @i=@i+1 end delete #xxx where ChildItem in (select b from @tmp1) set @j=@j+1 endset @i=1 insert @tmp2 select *,1 from #bom where parentitem=(select top 1 parentitem from #bom)while exists(select 1 from #bom a,@tmp2 b where a.parentitem=b.b and a.childitem not in (select b from @tmp2)) begin insert @tmp2 select a.*,@i+1 from #bom a,@tmp2 b where a.parentitem=b.b and a.childitem not in (select b from @tmp2) set @i=@i+1 endselect replicate('.',b.c-1)+rtrim(b.c) 层次,a.b from @tmp1 a,@tmp2 b where a.b=b.b order by a.d,a.c go drop table #bom,#xxx
declare @你的表 table (sortid int,parid int,sortname varchar(200)) insert @你的表 values(28,0,'包装') insert @你的表 values(63,0,'安全防护') insert @你的表 values(64,0,'办公') insert @你的表 values(73,0,'电脑') insert @你的表 values(74,63,'锁具') insert @你的表 values(75,63,'防盗门') insert @你的表 values(118,75,'普通防盗门') insert @你的表 values(119,75,'豪华防盗门') insert @你的表 values(120,28,'文化用纸') insert @你的表 values(121,28,'包装用纸') insert @你的表 values(122,28,'纸浆') insert @你的表 values(123,63,'保险柜') insert @你的表 values(124,63,'防身用具') insert @你的表 values(125,73,'主机 ') insert @你的表 values(126,73,'机箱') insert @你的表 values(127,73,'软件') insert @你的表 values(128,127,'财务软件') insert @你的表 values(129,127,'系统管理软件') insert @你的表 values(130,127,'工具软件软件') insert @你的表 values(131,127,'数据库软件') declare @tmp1 table (sortid int,parid int,sortname varchar(200),层次 int,所属分组 int) declare @i int,@j int select @i=1,@j=1while exists(select 1 from @你的表 where parid=0 and sortid not in (select sortid from @tmp1)) begin insert @tmp1 select top 1 *,@i,@j from @你的表 where parid=0 and sortid not in (select sortid from @tmp1) order by sortid while @@rowcount>0 begin set @i=@i+1 insert @tmp1 select a.*,@i,@j from @你的表 a,@tmp1 b where a.parid=b.sortid and a.sortid not in (select sortid from @tmp1) end select @j=@j+1,@i=1 end select * from @tmp1
--用自定义函数就行了.create function f_getid() returns @re table(id int,level int) as begin declare @level int set @level=0 insert into @re select sortid,@level from 表 where parid=0 while @@rowcount>0 begin set @level=@level+1 insert into @re select a.sortid,@level from 表 a inner join @re b on a.parid=b.id and b.level=@level-1 end return(@re) end go--调用自定义得到你的结果: select a.*,b.level as 层次 from 表 a inner join dbo.f_getid() b on a.sortid=b.id
函数错了一点,应该将:return(@re)改为:return下面是数据测试,在我的函数中,第一层为0,如果是1,就将set @level=0改为set @level=1create table 表(sortid int,parid int,sortname varchar(200)) insert 表 values(28,0,'包装') insert 表 values(63,0,'安全防护') insert 表 values(64,0,'办公') insert 表 values(73,0,'电脑') insert 表 values(74,63,'锁具') insert 表 values(75,63,'防盗门') insert 表 values(118,75,'普通防盗门') insert 表 values(119,75,'豪华防盗门') insert 表 values(120,28,'文化用纸') insert 表 values(121,28,'包装用纸') insert 表 values(122,28,'纸浆') insert 表 values(123,63,'保险柜') insert 表 values(124,63,'防身用具') insert 表 values(125,73,'主机 ') insert 表 values(126,73,'机箱') insert 表 values(127,73,'软件') insert 表 values(128,127,'财务软件') insert 表 values(129,127,'系统管理软件') insert 表 values(130,127,'工具软件软件') insert 表 values(131,127,'数据库软件')go --用自定义函数就行了. create function f_getid() returns @re table(id int,level int) as begin declare @level int set @level=0 insert into @re select sortid,@level from 表 where parid=0 while @@rowcount>0 begin set @level=@level+1 insert into @re select a.sortid,@level from 表 a inner join @re b on a.parid=b.id and b.level=@level-1 end return end go--调用自定义得到你的结果: select a.*,b.level as 层次 from 表 a inner join dbo.f_getid() b on a.sortid=b.id
create Table #BOM(ParentItem varchar(10), ChildItem varchar(10))
insert #bom values('A','B')
insert #bom values('A','C')
insert #bom values('A','D')
insert #bom values('A','E')
insert #bom values('B','F')
insert #bom values('B','G')
insert #bom values('D','H')
insert #bom values('D','I')
insert #bom values('F','J')
insert #bom values('F','K')--临时表
select * into #xxx from #bom--深度
declare @tmp1 table (a varchar(10),b varchar(10),c int,d int) --存树放顺序,及子树号
declare @tmp2 table (a varchar(10),b varchar(10),c int) --存放层次declare @i int,@j varchar(100)
set @j=1while exists (select 1 from #xxx)
begin
set @i=1
insert @tmp1 select top 1 *,@i,@j from #xxx
while exists(select 1 from #xxx a,@tmp1 b where a.parentitem=b.b and a.childitem not in (select b from @tmp1))
begin
insert @tmp1 select top 1 a.*,@i+1,@j from #xxx a,@tmp1 b where a.parentitem=b.b and a.childitem not in (select b from @tmp1) order by a.parentitem desc
set @i=@i+1
end
delete #xxx where ChildItem in (select b from @tmp1)
set @j=@j+1
endset @i=1
insert @tmp2 select *,1 from #bom where parentitem=(select top 1 parentitem from #bom)while exists(select 1 from #bom a,@tmp2 b where a.parentitem=b.b and a.childitem not in (select b from @tmp2))
begin
insert @tmp2 select a.*,@i+1 from #bom a,@tmp2 b where a.parentitem=b.b and a.childitem not in (select b from @tmp2)
set @i=@i+1
endselect replicate('.',b.c-1)+rtrim(b.c) 层次,a.b from @tmp1 a,@tmp2 b where a.b=b.b order by a.d,a.c
go
drop table #bom,#xxx
insert @你的表 values(28,0,'包装')
insert @你的表 values(63,0,'安全防护')
insert @你的表 values(64,0,'办公')
insert @你的表 values(73,0,'电脑')
insert @你的表 values(74,63,'锁具')
insert @你的表 values(75,63,'防盗门')
insert @你的表 values(118,75,'普通防盗门')
insert @你的表 values(119,75,'豪华防盗门')
insert @你的表 values(120,28,'文化用纸')
insert @你的表 values(121,28,'包装用纸')
insert @你的表 values(122,28,'纸浆')
insert @你的表 values(123,63,'保险柜')
insert @你的表 values(124,63,'防身用具')
insert @你的表 values(125,73,'主机 ')
insert @你的表 values(126,73,'机箱')
insert @你的表 values(127,73,'软件')
insert @你的表 values(128,127,'财务软件')
insert @你的表 values(129,127,'系统管理软件')
insert @你的表 values(130,127,'工具软件软件')
insert @你的表 values(131,127,'数据库软件')
declare @tmp1 table (sortid int,parid int,sortname varchar(200),层次 int,所属分组 int)
declare @i int,@j int
select @i=1,@j=1while exists(select 1 from @你的表 where parid=0 and sortid not in (select sortid from @tmp1))
begin
insert @tmp1 select top 1 *,@i,@j from @你的表 where parid=0 and sortid not in (select sortid from @tmp1) order by sortid
while @@rowcount>0
begin
set @i=@i+1
insert @tmp1 select a.*,@i,@j from @你的表 a,@tmp1 b where a.parid=b.sortid and a.sortid not in (select sortid from @tmp1)
end
select @j=@j+1,@i=1
end
select * from @tmp1
----------- ----------- -------------------- ----------- -----------
28 0 包装 1 1
120 28 文化用纸 2 1
121 28 包装用纸 2 1
122 28 纸浆 2 1
63 0 安全防护 1 2
74 63 锁具 2 2
75 63 防盗门 2 2
123 63 保险柜 2 2
124 63 防身用具 2 2
118 75 普通防盗门 3 2
119 75 豪华防盗门 3 2
64 0 办公 1 3
73 0 电脑 1 4
125 73 主机 2 4
126 73 机箱 2 4
127 73 软件 2 4
128 127 财务软件 3 4
129 127 系统管理软件 3 4
130 127 工具软件软件 3 4
131 127 数据库软件 3 4(所影响的行数为 20 行)
中的"@你的表"是随着我的类别表而变化的!!!请指教!!
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1409876
returns @re table(id int,level int)
as
begin
declare @level int
set @level=0
insert into @re select sortid,@level from 表 where parid=0
while @@rowcount>0
begin
set @level=@level+1
insert into @re select a.sortid,@level from 表 a inner join @re b on a.parid=b.id and b.level=@level-1
end
return(@re)
end
go--调用自定义得到你的结果:
select a.*,b.level as 层次 from 表 a inner join dbo.f_getid() b on a.sortid=b.id
insert 表 values(28,0,'包装')
insert 表 values(63,0,'安全防护')
insert 表 values(64,0,'办公')
insert 表 values(73,0,'电脑')
insert 表 values(74,63,'锁具')
insert 表 values(75,63,'防盗门')
insert 表 values(118,75,'普通防盗门')
insert 表 values(119,75,'豪华防盗门')
insert 表 values(120,28,'文化用纸')
insert 表 values(121,28,'包装用纸')
insert 表 values(122,28,'纸浆')
insert 表 values(123,63,'保险柜')
insert 表 values(124,63,'防身用具')
insert 表 values(125,73,'主机 ')
insert 表 values(126,73,'机箱')
insert 表 values(127,73,'软件')
insert 表 values(128,127,'财务软件')
insert 表 values(129,127,'系统管理软件')
insert 表 values(130,127,'工具软件软件')
insert 表 values(131,127,'数据库软件')go
--用自定义函数就行了.
create function f_getid()
returns @re table(id int,level int)
as
begin
declare @level int
set @level=0
insert into @re select sortid,@level from 表 where parid=0
while @@rowcount>0
begin
set @level=@level+1
insert into @re select a.sortid,@level from 表 a inner join @re b on a.parid=b.id and b.level=@level-1
end
return
end
go--调用自定义得到你的结果:
select a.*,b.level as 层次 from 表 a inner join dbo.f_getid() b on a.sortid=b.id