CREATE TABLE [dbo].[tree] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[父亲ID] [int] NULL ,
[名字] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into tree values(0,'祖宗')
insert into tree values(1,'山本1')
insert into tree values(1,'山本2')
insert into tree values(1,'山本3')
insert into tree values(1,'山本4')
insert into tree values(1,'山本5')
insert into tree values(2,'山本11')
insert into tree values(2,'山本12')go
create function bom (@id int)
returns @tb table (ID int,父亲ID int,名字 varchar(50))
as
begin
insert @tb select ID,父亲ID,名字 from tree where 父亲ID = @id
while @@rowcount > 0
insert @tb select ID,父亲ID,名字 from tree
where 父亲ID in (select ID from @tb)
and ID not in (select ID from @tb)
return
endgoselect * from dbo.bom(1)
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[父亲ID] [int] NULL ,
[名字] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into tree values(0,'祖宗')
insert into tree values(1,'山本1')
insert into tree values(1,'山本2')
insert into tree values(1,'山本3')
insert into tree values(1,'山本4')
insert into tree values(1,'山本5')
insert into tree values(2,'山本11')
insert into tree values(2,'山本12')go
create function bom (@id int)
returns @tb table (ID int,父亲ID int,名字 varchar(50))
as
begin
insert @tb select ID,父亲ID,名字 from tree where 父亲ID = @id
while @@rowcount > 0
insert @tb select ID,父亲ID,名字 from tree
where 父亲ID in (select ID from @tb)
and ID not in (select ID from @tb)
return
endgoselect * from dbo.bom(1)
--处理函数
create function f_id()
returns @re table(id int,idstr varchar(8000),level int)
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20),@l int
select @l=1
,@idlen=max(len(id))
,@idheader=space(@idlen)
from tree insert @re select id,right(@idheader+cast(id as varchar),@idlen),@l
from tree where 父亲ID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,b.idstr+','+right(@idheader+cast(a.id as varchar),@idlen),@l
from tree a join @re b on a.父亲ID=b.id
where b.level=@l-1
end
return
end
go
既:
ID 父亲ID 名字 儿子名单
1 0 祖宗 山本1,山本2,山本3,山本4,山本5,山本11,山本12
2 1 山本1 山本2,山本3,山本4,山本5,山本11,山本12
select a.*,b.idstr
from tree a join f_id() b on a.id=b.id
and b.id=2 --查询id=2的
--2,用逗号分开的全部后代名单字符串。
select a.*,b.idstr
from tree a join f_id() b on a.id=b.id --3,辈份数。,
select a.*,b.level
from tree a join f_id() b on a.id=b.id --4,在同父兄弟中的排行数 。
select a.*,b.idstr,排行数=(
select sum(1) from f_id() where level=b.level
and idstr like left(b.idstr,len(b.idstr)-charindex(',',reverse(b.idstr)))+'%'
and idstr<=b.idstr)
from tree a join f_id() b on a.id=b.id --5,排序
select a.id,a.父亲id,space(level*2)+a.名字
from tree a join f_id() b on a.id=b.id
order by b.idstr
测试语句:
SELECT tree.id, tree.名字, tree.父亲ID, info.idstr, info. LEVEL
FROM tree INNER JOIN
(SELECT *
FROM f_id() f_id) info ON info.id = tree.ID
结果:id 名字 父亲ID idstr LEVEL
1 祖宗 0 1 1
2 山本1 1 1,2 2
3 山本2 1 1,3 2
4 山本3 1 1,4 2
5 山本4 1 1,5 2
6 山本5 1 1,6 2
7 山本11 2 1,2,7 3
8 山本12 2 1,2,8 3
SELECT a.*, b.idstr AS Expr1
FROM tree a INNER JOIN
f_id() b ON a.ID = b.id
ID 父亲ID 名字 Expr1
1 0 祖宗 1
2 1 山本1 1,2
3 1 山本2 1,3
4 1 山本3 1,4
5 1 山本4 1,5
6 1 山本5 1,6
7 2 山本11 1,2,7
8 2 山本12 1,2,8
create function f_name(@id int)
returns varchar(8000)
as
begin
declare @re table(id int,name varchar(50),level int)
declare @l int,@r varchar(8000)
set @l=0
insert into @re select id,名字,@l
from tree where 父亲ID=@id
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.id,a.名字,@l
from tree a inner join @re b on a.父亲ID=b.id
where b.level=@l-1
end
set @r=''
select @r=@r+','+name from @re
return(substring(@r,2,8000))
end
go
select dbo.f_name(0)--2,用逗号分开的全部后代名单字符串。
select *,dbo.f_name(id)
from tree
CREATE function f_childID(@id int)
returns varchar(8000)
as
begin
declare @re table(id int,level int)
declare @l int,@r varchar(8000)
set @l=0
insert into @re select id,@l
from tree where 父亲ID=@id
while @@rowcount>0
begin
set @l=@l+1
insert into @re select a.id,@l
from tree a inner join @re b on a.父亲ID=b.id
where b.level=@l-1
end
set @r=''
select @r=@r+','+ltrim(str(id) ) from @re
return(substring(@r,2,8000))
end1 0 祖宗 2,3,4,5,6,7,8
2 1 山本1 7,8
3 1 山本2
4 1 山本3
5 1 山本4
6 1 山本5
7 2 山本11
8 2 山本12