如何在sql Server中将多个表联接在一起

smtd7mpg  于 2022-12-03  发布在  SQL Server
关注(0)|答案(2)|浏览(264)
create table mentor (studentid int,teacherid int);
insert into mentor values(1,3),(2,1),(3,3),(4,2),(5,1),(6,2);
select * from mentor;
create table studRegister (studentid int,studName nvarchar(30));
insert into studRegister values(1,'Remo'),(2,'Jack'),(3,'Mike'),(4,'Mahesh'),(5,'Shinku'),(6,'Bond');
select * from studRegister ;
create table teacher(teacherId int, teacherName nvarchar(30));
insert into teacher values(1,'Mr.Moffer'),(2,'Mrs.Lisa'),(3,'Mr.Danny');
select * from teacher;

学生表:

导师表

教师表

不使用子查询,请联接表

预期输出:

| 学生Name|教师Name|
| - -|- -|
| 雷莫| Mr.Danny |
| 杰克| Mr.Moffer |
| 麦克| Mr.Danny |
| 马赫什| Mrs.Lisa |
| 新久| Mr.Moffer |
| 债券| Mrs.Lisa |

ruarlubt

ruarlubt1#

SELECT        studRegister.studName, teacher.teacherName
FROM            mentor INNER JOIN
                         studRegister ON mentor.studentid = studRegister.studentid INNER JOIN
                         teacher ON mentor.teacherid = teacher.teacherId
yb3bgrhw

yb3bgrhw2#

为了获得所需的结果,您需要将studRegister中的StudName列与teacher表中相应的teacherName组合在一起。
为此,您需要对所有三个表的共有列执行Join操作。mentor表是包含与其他表相似的列的表,因此,将studRegistermentor表连接到其共享列StudentId,然后将mentorteacher表连接到其teacherIdcolumns将从所有三个表中合并所有列。
通过仅选择StudNameteacherName,您将仅查询这2列,而不会在结果中查询任何其他列。
查询如下所示:

SELECT StudName AS StudentName, teacherName AS TeacherName FROM studRegister sR 
    INNER JOIN mentor m ON sR.studentId = m.StudentId
    INNER JOIN teacher t ON t.teacherId = m.teacherId

相关问题