这样的SQL语句怎么写，一个表有多个字段对应另一个表中的同一字段？

如：User表(U)：UserID      UserName
                 1            A
                 2            B

    Info表(I)：InfoID      StudentID       TeacherID
                100           1                2
其中StudentID、TeacherID都是存的UserID

要得到的结果是：I.InfoID  I.StudentID  U.UserName I.TeacherID   U.UserName
                   100        1             A          2             B

当然要一个SQL语句搞定，谢谢！

7 个解决方案

#1

select i.infoid,i.studentid,u.username,i.teacherid,uu.username
from info表 i left join user表 u on u.userid = i.studentid
left join user表 uu on uu.userid = i.teacherid

#2

Select a.InfoID,a.StudentID,b.UserName,a.TeacherID,c.UserName
from Info a
join User b on a.StudentID = b.UserID
join User c on a.TeacherID = c.UserID

#3

select InfoID,StudentID
,UserName=(select top 1 username from user where studentid=a.studentid)
,TeacherID
,UserName1=(select top 1 username from user where TeacherID=a.TeacherID)
from Info a

#4

多谢！但如果再要关联其它表进行条件查询，会不会受影响
再者这样关联对效率的影响有多大

#5

select InfoID,StudentID,(select username from user where studentid=a.studentid) UserName,TeacherID,(select username from user where TeacherID=a.TeacherID) UserName1 from Info a

#6

select InfoID,StudentID,(select UserName from User where UserIDa.StudentID) UserName1,TeacherID,(select UserName from user where UserID=a.TeacherID) UserName2 from Info a

#7

thanks，问题已经解决，给分

#1