关于一个数据追述的问题,请大家帮忙给看看呀!
问题如下:

表A 字段A 字段B
A B
B C
C D
D E


表B 字段C 字段D
aa A
bb B
CC C
dd D
ee E


请问大侠:在表A中如何通过"E"找到"D",然后通过"D"找到"C",然后通过"C"找到"B",然后通过"B"找到"A".然后分离出A,B,C,D,E
在表B中 用"select * from 表B where 字段D=A or 字段D=B or 字段D=C or 字段D=D or 字段D=E"语句找出相应的记录!从而完成表的追述!谢谢大家帮 助!




[解决办法]

SQL code

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)asbegin     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 @cidsendselect * 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