问题如下:
表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 [email protected] while(@@rowcount>0) begin set @[email protected]+1 insert into @t select a.*,@lev from a a,@t b where a.b=b.a and [email protected] end declare @cids varchar(500) select @cids=isnull(@cids+',','')+ltrim(b) from @t order by lev select top 1 @[email protected]+','+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 [email protected])
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 [email protected]+''' or b='''+left(@nc,len(@nc)-6)
else
set @nc= 'select * from tba where [email protected]+''''
exec (@nc)
drop FUNCTION 查询
drop TABLE tb
drop TABLE tba