给一个参考方案:
create table aa(id varchar(8) ,name varchar(16),parentid varchar(8))
create table bb(sid varchar(64))
insert into aa select
'1', '中国', '0' union all select
'2', '湖南', '1' union all select
'3', '河南', '1' union all select
'4', '北京', '1' union all select
'5', '美国', '0' union all select
'6', '纽约', '5'
insert into bb select
'2,3' union all select
'2,4' union all select
'3,4' union all select
'5'
CREATE FUNCTION [dbo].[A_id] (@id varchar(64))
RETURNS varchar(128) AS
BEGIN
declare @l_id varchar(64),@A_id varchar(128)
set @l_id = @id+','
set @A_id = ''
while charindex(',',@l_id) > 0
begin
declare @f_id varchar(8)
set @f_id = left(@l_id,charindex(',',@l_id)-1)
set @A_id = @A_id + '[' + @f_id + ']'
while exists(select 1 from aa where id=@f_id and parentid <> 0)
begin
select @f_id = parentid from aa where id=@f_id and parentid <> 0
set @A_id = @A_id + '[' + @f_id + ']'
end
set @l_id = right(@l_id,len(@l_id)-charindex(',',@l_id))
end
return @A_id
END
go
declare @q_id varchar(32)
set @q_id = '2'
select sid from bb where charindex('['+@q_id+']',dbo.A_id(sid)) > 0
sid
----------------------------------------------------------------
2,3
2,4
--建立环境
create table a(
id int,
name varchar(10),
parentid int
)
create table b(
id int,
C varchar(20)
)
--插入数据
insert a
select 1,'1',0
union all select
2,'2',0
union all select
3,'3',0
union all select
11,'11',1
union all select
12,'12',1
union all select
13,'13',1
union all select
21,'21',2
union all select
22,'22',2
union all select
111,'111',11
union all select
112,'112',11
union all select
131,'131',13
union all select
211,'211',21
insert b
select 1,'13,111,211'
union all
select 2,'3,11,13'
union all
select 3,'1,2'
--建立函数
create function fn_ab(
@bid int
)
returns @r table (
bid int,
aid int,
name varchar(10),
parentid int
)
as
begin
--拆分id
declare @c varchar(20)
declare @t table (
id int
)
select @c=c from b where id=@bid
if isnull(@c,'')='' return
declare @i int
set @i=charindex(',',@c)
while @i>0
begin
insert @t values(left(@c,@i-1))
set @c=stuff(@c,1,@i,'')
set @i=charindex(',',@c)
end
if @c<>'' insert @t values(@c)
--初步结果
insert @r select @bid,* from a where id in (select id from @t)
--取子id
while exists (select 1
from a
where parentid in (select aid from @r)
and id not in (select aid from @r)
)
insert @r select @bid,*
from a
where parentid in (select aid from @r)
and id not in (select aid from @r)
return
end
go
--调用
select * from dbo.fn_ab(1)
--结果
bid aid name parentid
----------- ----------- ---------- -----------
1 13 13 1
1 111 111 11
1 211 211 21
1 131 131 13