select (select Teacher_Name from Teacher where Teacher_ID=Course_1_Teacher) AS Course_1_Teacher,
(select Teacher_Name from Teacher where Teacher_ID=Course_2_Teacher) AS Course_2_Teacher,
(select Teacher_Name from Teacher where Teacher_ID=Course_3_Teacher) AS Course_3_Teacher
from Course
(select Teacher_Name from Teacher where Teacher_ID=Course_2_Teacher) AS Course_2_Teacher,
(select Teacher_Name from Teacher where Teacher_ID=Course_3_Teacher) AS Course_3_Teacher
from Course
from Course left join Teacher as T1 on Teacher_ID=Course_1_Teacher
left join Teacher as T2 on Teacher_ID=Course_2_Teacher
left join Teacher as T3 on Teacher_ID=Course_3_Teacher
--測試數據
declare @Teacher table
(
Teacher_ID int
,Teacher_Name nvarchar(10)
);
declare @Course table
(
Course_1_Teacher int
,Course_2_Teacher int
,Course_3_Teacher int
);
insert into @Teacher
values(1,'王三')
,(2,'张武')
,(3,'李六');insert into @Course
values(1,2,3);
--查詢
select ISNULL(t2.Teacher_Name,'') Course_1_Teache
,ISNULL(t3.Teacher_Name,'') Course_2_Teacher
,ISNULL(t4.Teacher_Name,'') Course_3_Teacher
from @Course t1
left join @Teacher t2 on t1.Course_1_Teacher=t2.Teacher_ID
left join @Teacher t3 on t1.Course_2_Teacher=t3.Teacher_ID
left join @Teacher t4 on t1.Course_3_Teacher=t4.Teacher_ID