最后20分,求1条SQL

[nlpz]

id name
1 小张
2 小李
3 小陈


id1 id2 id3
1 3 2
3 2 2

要求结果-----------------
id1 id2 id3
小张 小陈 小李
小陈 小李 小李

[sql2000vfp]

join

[黄_瓜]

left join

[csdyyr]

select (select name from tb where id=id1) as id1
from tb2

[黄_瓜]

select a.name,b.name,c.name from tb t
left join ta  a on t.id1=a.id
left join ta  b on t.id2=b.id
left join ta  b on t.id3=c.id

[永生天地]

[Quote=引用 3 楼 beirut 的回复:]
SQL code
select a.name,b.name,c.name from tb t
left join ta a on t.id1=a.id
left join ta b on t.id2=b.id
left join ta b on t.id3=c.id
[/Quote]
ok?
动态？

[bancxc]

--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (id int,name varchar(4))
insert into #T1
select 1,'小张' union all
select 2,'小李' union all
select 3,'小陈'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (id1 int,id2 int,id3 int)
insert into #T2
select 1,3,2 union all
select 3,2,2
 
select t1.name n1,t2.name n2,t3.name n3 from #T2 t
left join #t1 t1 on t1.id=t.id1
left join #t1 t2 on t2.id=t.id2
left join #t1 t3 on t3.id=t.id3

n1   n2   n3
---- ---- ----
小张   小陈   小李
小陈   小李   小李

(2 行受影响)

[dawugui]

select t1.name,
t2.name,
t3.name
from b
inner join a t1 on b.id1 = t1.id
inner join a t2 on b.id2 = t1.id
inner join a t3 on b.id3 = t1.id

[xiaoping04408]

在sql中可以做个自定义函数 根据编号返回名称如：getnamebyid
getnamebyid()实现楼主自己搞定！

select getnamebyid(id1),getnamebyid(id2),getnamebyid(id3) from table2

[xuam]

select a.name,b.name,c.name from T2 left join T a on a.id =T2.id1
left join T b on b.id =T2.id2
left join T c on c.id =T2.id3

[htl258_Tony]

直接用JOIN就可以了。

[dawugui]

create table a (id int,name varchar(4))
insert into a
select 1,'小张' union all
select 2,'小李' union all
select 3,'小陈'
create table b (id1 int,id2 int,id3 int)
insert into b
select 1,3,2 union all
select 3,2,2
go

select t1.name,
       t2.name,
       t3.name
from b
inner join a t1 on b.id1 = t1.id
inner join a t2 on b.id2 = t2.id
inner join a t3 on b.id3 = t3.id


drop table a , b

/*
name name name 
---- ---- ---- 
小张   小陈   小李
小陈   小李   小李

（所影响的行数为 2 行）
*/