关于一个数据追述的问题,请大家帮忙给看看呀!
问题如下:
表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"语句找出相应的记录!从而完成表的追述!谢谢大家帮 助!
[解决办法]
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