日期:2014-05-18 浏览次数:20643 次
create table a(a varchar(10),b varchar(10)) insert into a select 'a','b' insert into a select 'b','c' insert into a select 'c','d' insert into a select 'd','e' create table b(c varchar(10),d varchar(10)) insert into b select 'aa','a' insert into b select 'bb','b' insert into b select 'dc','c' insert into b select 'dd','d' insert into b select 'ee','e' create function f_cid(@b varchar(10)) returns varchar(500) as begin declare @t table(a varchar(10),b varchar(10),lev int) declare @lev int set @lev=1 insert into @t select *,@lev from a where b=@b while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.*,@lev from a a,@t b where a.b=b.a and b.lev=@lev-1 end declare @cids varchar(500) select @cids=isnull(@cids+',','')+ltrim(b) from @t order by lev select top 1 @cids=@cids+','+a from @t order by lev desc return @cids end select * from B where charindex(','+d+',',','+dbo.f_cid('e')+',')>0
------解决方案--------------------
CREATE TABLE tb (a char(8),b char(8))
CREATE TABLE tba (a char(8),b char(8))
insert into tb
select 'A','B'
union all
select 'B','C'
union all
select 'C','D'
union all
select 'D','E'
insert into tba
select 'Aa','A'
union all
select 'bb','B'
union all
select 'Cc','C'
union all
select 'Dd','D'
union all
select 'ee','E'
CREATE FUNCTION 查询 (
@pp char(8) )
returns nvarchar(4000)
AS
begin
declare @ii nvarchar(4000)
set @ii=(select top 1 a from tb where b=@pp)
if (@ii is not null)
set @ii=rtrim(@ii)+''' or b='''+rtrim(dbo.查询(@ii))
else
set @ii=''
return @ii
end
declare @a char(8),@nc nvarchar(4000)
set @a='E'
set @nc=dbo.查询(@a)
if (ltrim(@nc)<>'')
set @nc='select * from tba where b='''+@a+''' or b='''+left(@nc,len(@nc)-6)
else
set @nc= 'select * from tba where b='''+@a+''''
exec (@nc)
drop FUNCTION 查询
drop TABLE tb
drop TABLE tba