SQL 如何在SQL中合并来自多个表的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5575682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to merge data from multiple tables in SQL
提问by Null Head
Guess I am in a complex situation. Heres the scene.
I have 3 tables in SQL Server.
Enrollment, Student, Course. (FYI, there are no foreign keys)
The table columns are
Student- StudentId, StudentName
Course- CourseId, CourseName
Enrollment- EnrollmentId, StudentId, CourseId, CourseResult
猜猜我处于一个复杂的情况。场景来了
我在 SQL Server 中有 3 个表。
招生，学生，课程。（仅供参考，没有外键）
表列是
Student- StudentId、StudentName
Course- CourseId、CourseName
Enrollment- EnrollmentId、StudentId、CourseId、CourseResult
Sample Data
Student - s1, Sid
Course - c1, Science
Enrollment - 1, s1, c1, 80
样本数据
学生 - s1，Sid
课程 - c1，科学
入学 - 1, s1, c1, 80
I would want a SQL query that selects data like below
我想要一个选择如下数据的 SQL 查询
1, s1, Sid, c1, Science, 80
1, s1, Sid, c1, 科学, 80
I did it hard way in the DAL layer with multiple calls to database. But would like to do it in one call, and in DB.
我通过多次调用数据库在 DAL 层中做到了。但是想在一次调用中完成，并且在 DB 中完成。
Any one for the rescue!
任何一个来救援！
回答by Richard Schneider
Use a join.
使用连接。
select enrollment.*, student.*, course.* from enrollment
inner join Student on enrollment.studentId = student.studentId
inner join Course on enrollment.courseId = course.courseId
回答by Paul Sasik
There actually are foreign keys in your data model. They may not be marked as such in the db or you don't realize it. I also think it would become clearer if in your list you put the Enrollment table second, between Student and Course. Enrollment is the table that links a student to a course.
您的数据模型中实际上有外键。它们可能没有在数据库中标记为这样，或者您没有意识到。我还认为，如果在您的列表中将 Enrollment 表放在第二位，在 Student 和 Course 之间，它会变得更加清晰。注册是将学生链接到课程的表。
I think you just want an inner joinon the three tables like this:
我认为您只需要像这样对三个表进行内部联接：
SELECT e.EnrollmentId, s.StudentId, c.CourseId, c.CourseName, e.CourseResult
FROM Student AS s
JOIN Enrollment AS e ON s.StudentId = e.StudentId
JOIN Course AS c on e.CourseId = c.CourseId
回答by Kon
Seems like a few simple JOINs would do it...
似乎一些简单的 JOIN 就可以做到...
select
e.EnrollmentId
,s.StudentId
,s.StudentName
,c.CourseId
,c.CourseName
,e.CourseResult
from Enrollement e
inner join Course c on e.CourseId = c.CourseId
inner join Student s on e.StudentId = s.StudentId
回答by dgilland
Try:
尝试：
SELECT e.EnrollmentId, s.StudentId, s.StudentName, c.CourseId, c.CourseName, e.CourseResult
FROM Student s JOIN
Enrollment e ON s.StudentId = e.StudentId JOIN
Course c ON e.CourseId = c.CourseId
