SELECT B.ID, B.QK, B.AD, N1=A1.NA, B.BD, N2=A2.NA, B.CD, N3=A3.NA FROM TB表 B LEFT JOIN TA表 A1 ON B.AD=A1.ID LEFT JOIN TA表 A2 ON B.BD=A2.ID LEFT JOIN TA表 A3 ON B.CD=A3.ID ORDER BY B.ID
create table ta (id int,nu varchar(20),na varchar(20))insert ta select 1,'ab','a1' union all select 2,'ca','a2' union all select 3,'qd','a3'create table tb (id int,qk varchar(20),ad int,bd int,cd int)insert tb select 1,'aa',1,null,null union all select 2,'bb',2,1,null union all select 3,'cc',3,null,2select a.id,b.qk,ad,N1=(select na from ta where id=b.ad) ,b.bd,N2=(select na from ta where id=b.bd), cd,N3=(select na from ta where id=b.cd) from ta a,tb b where a.id=b.id order by a.iddrop table ta drop table tbid qk ad N1 bd N2 cd N3 ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- 1 aa 1 a1 NULL NULL NULL NULL 2 bb 2 a2 1 a1 NULL NULL 3 cc 3 a3 NULL NULL 2 a2(所影响的行数为 3 行)
B.ID,
B.QK,
B.AD,
N1=A1.NA,
B.BD,
N2=A2.NA,
B.CD,
N3=A3.NA
FROM
TB表 B
LEFT JOIN
TA表 A1
ON
B.AD=A1.ID
LEFT JOIN
TA表 A2
ON
B.BD=A2.ID
LEFT JOIN
TA表 A3
ON
B.CD=A3.ID
ORDER BY
B.ID
(id int,nu varchar(20),na varchar(20))insert ta
select 1,'ab','a1' union all
select 2,'ca','a2' union all
select 3,'qd','a3'create table tb
(id int,qk varchar(20),ad int,bd int,cd int)insert tb
select 1,'aa',1,null,null union all
select 2,'bb',2,1,null union all
select 3,'cc',3,null,2select a.id,b.qk,ad,N1=(select na from ta where id=b.ad)
,b.bd,N2=(select na from ta where id=b.bd),
cd,N3=(select na from ta where id=b.cd)
from ta a,tb b where a.id=b.id order by a.iddrop table ta
drop table tbid qk ad N1 bd N2 cd N3
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- --------------------
1 aa 1 a1 NULL NULL NULL NULL
2 bb 2 a2 1 a1 NULL NULL
3 cc 3 a3 NULL NULL 2 a2(所影响的行数为 3 行)