比如表A
ID TITLE1 TITLE2 TITLE3
1 AA1 BB1 CC1
2 AA2 BB2 CC2
3 AA3 BB3 CC3
4 AA4 BB4 CC4
表B
ID A_ID1 A_ID2
1 1 3应用时,需要得到表B的结果为1 1 3 AA1 BB1 CC1 AA3 BB3 CC3本人的SQL十分低效
Select B.*,
(SELECT A.TITLE1 FROM A Where A.ID = B.AID1) ID1_TITLE1,
(SELECT A.TITLE2 FROM A Where A.ID = B.AID1) ID1_TITLE2,
(SELECT A.TITLE3 FROM A Where A.ID = B.AID1) ID1_TITLE3,
(SELECT A.TITLE1 FROM A Where A.ID = B.AID2) ID2_TITLE3,
(SELECT A.TITLE2 FROM A Where A.ID = B.AID2) ID2_TITLE3,
(SELECT A.TITLE3 FROM A Where A.ID = B.AID2) ID2_TITLE3
From B
求高效脚本...或方法
ID TITLE1 TITLE2 TITLE3
1 AA1 BB1 CC1
2 AA2 BB2 CC2
3 AA3 BB3 CC3
4 AA4 BB4 CC4
表B
ID A_ID1 A_ID2
1 1 3应用时,需要得到表B的结果为1 1 3 AA1 BB1 CC1 AA3 BB3 CC3本人的SQL十分低效
Select B.*,
(SELECT A.TITLE1 FROM A Where A.ID = B.AID1) ID1_TITLE1,
(SELECT A.TITLE2 FROM A Where A.ID = B.AID1) ID1_TITLE2,
(SELECT A.TITLE3 FROM A Where A.ID = B.AID1) ID1_TITLE3,
(SELECT A.TITLE1 FROM A Where A.ID = B.AID2) ID2_TITLE3,
(SELECT A.TITLE2 FROM A Where A.ID = B.AID2) ID2_TITLE3,
(SELECT A.TITLE3 FROM A Where A.ID = B.AID2) ID2_TITLE3
From B
求高效脚本...或方法
select B.ID,
B.A_ID1,
B.A_ID2,
A1.TITLE1,
A1.TITLE2,
A1.TITLE3,
A2.TITLE1,
A2.TITLE2,
A2.TITLE3
from B
left outer join A A1
on A1.ID = B.A_ID1
left outer join A A2
on A2.ID = B.A_ID2