!!在线请教高手多表联合查询问题.....

[cefriend]

A表
id Aname  tablename  tid
1   aa       B       01B
2   fff      C       01C

B表
id   Bname  
01B   bb       

C表
id   Bname  
01C   cc       
如上图所示三张表,,想做联合查询得出  A表记录得到B表,与C表记录  条件是  根据A表中 tablename 字段得到要查询的表名,tid字段是查询的外键.想请都大家如何查询
select A.*,B.* from A inner join (select top 1 tablename from A) c on A.tid=c.id 想用子查询做出来,好像不成,只有用到动态SQL,不知道高手们有什么做法????

[liang145]

create table #A表
(id int,Aname nvarchar(10), tablename nvarchar(2), tid nvarchar(10))
insert #A表
select 1  , 'aa' ,'B', '01B' union all
select 2  , 'fff','C', '01C' 

create table #B表
(id nvarchar(10),  Bname nvarchar(10))
insert #B表
select '01B', 'bb'       

create table #C表
(id nvarchar(10),  Bname  nvarchar(10))
insert #C表
select '01C',   'cc'

select a.*,
case tablename when 'B' then isnull(b.id,'') 
               when 'c' then isnull(c.id,'')
               else '' end as 'BID',
case tablename when 'B' then isnull(b.Bname,'') 
                          when 'c' then isnull(c.Bname,'')
                          else '' end as 'BName'
from #A表 as a
left join #B表 as b on a.tid=b.id and a.tablename='B'
left join #C表 as c on a.tid=c.id and a.tablename='C'

[cd731107]

字段名和表名间接使用的化只有使用动态SQL，

[cefriend]

有没有用自定义函数做出来过............

[-晴天]

如果表是固定的,则没必要做动态:

create table tb(id int,Aname varchar(10),tablename varchar(10),tid varchar(10))
insert into tb select 1,'aa','B','01B'
insert into tb select 2,'fff','C','01C'
create table B表(id varchar(10),Bname varchar(10))
insert into B表 select '01B','bb'
create table C表(id varchar(10),Bname varchar(10))
insert into C表 select '01C','cc'
go
select id,Aname,
(case 
when tablename='B' then
	(select Bname from B表 where id=a.tid)
when tablename='C' then
	(select Bname from C表 where id=a.tid)
end)as bname
from tb a
go
drop table tb,B表,C表
/*
id          Aname      bname
----------- ---------- ----------
1           aa         bb
2           fff        cc

(2 行受影响)

*/

[cefriend]

看来还是只能用动态SQL了,,,刚才试了自定义函数也不成

Create function gTable(@pa varchar(20))
returns varchar(50)
as
begin 
	declare @s varchar(50);
	select @s =tablename from BusPact where ContractState=@pa;
	return @s ;
end

select * from A inner join getTable('1') ..........只能当变量用