declare @a表 table (id int,name varchar(2)) insert into @a表 select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd'declare @b表 table (id int,x int,y int,z int) insert into @b表 select 1,4,2,3 union all select 2,1,2,3select b.id,x=a1.name,y=a2.name,z=a3.name from @b表 b ,@a表 a1,@a表 a2,@a表 a3 where b.x=a1.id and b.y=a2.id and b.z=a3.id /* id x y z ----------- ---- ---- ---- 1 dd bb cc 2 aa bb cc */
select id,x=(select name from a where a.id=x), y=(select name from a where a.id=y), z=(select name from a where a.id=z), from b
最后要的是a表的行 比如b表id=2 结果应该是 id name 1 aa 2 bb 3 cc 3
-你难道要这样的结果?declare @a table (id int,name varchar(2)) insert into @a select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd'declare @b table (id int,x int,y int,z int) insert into @b select 1,4,2,3 union all select 2,1,2,3Select b.id,a.* From @a a Inner Join @b b on a.id =b.x or a.id=b.y or a.id=b.z
declare @a表 table (id int,name varchar(2))
insert into @a表
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'declare @b表 table (id int,x int,y int,z int)
insert into @b表
select 1,4,2,3 union all
select 2,1,2,3select b.id,x=a1.name,y=a2.name,z=a3.name from @b表 b
,@a表 a1,@a表 a2,@a表 a3
where b.x=a1.id and b.y=a2.id and b.z=a3.id
/*
id x y z
----------- ---- ---- ----
1 dd bb cc
2 aa bb cc
*/
select id,x=(select name from a where a.id=x),
y=(select name from a where a.id=y),
z=(select name from a where a.id=z),
from b
最后要的是a表的行
比如b表id=2 结果应该是
id name
1 aa
2 bb
3 cc
3
-你难道要这样的结果?declare @a table (id int,name varchar(2))
insert into @a
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'declare @b table (id int,x int,y int,z int)
insert into @b
select 1,4,2,3 union all
select 2,1,2,3Select b.id,a.* From @a a Inner Join @b b on a.id =b.x or a.id=b.y or a.id=b.z