MySQL 如何在SQL中通过多列连接两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26916741/
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 Join two tables by multiple columns in SQL?
提问by JasonSmith
I have two tables Evalulationand Value
我有两张桌子Evalulation和Value
In both tables, there are four columns. But three of the four are the same. In other words, they both have CaseNum, FileNum, ActivityNum. In addition to those columns, Evaluationhas column Gradeand Valuehas column Score.
在两个表中，都有四列。但四个中的三个是相同的。换句话说，它们都有CaseNum, FileNum, ActivityNum。除了这些列之外，Evaluation还有 has columnGrade和Valuehas column Score。
I want to merge the two into one table by CaseNum, FileNum, ActivityNumso I have a new table of 5 columns with both Valueand Scorein it.
我想通过合并两成一个表CaseNum，FileNum，ActivityNum所以我有5列的新表都Value和Score它。
Can I use Inner Joinmultiple times to do this?
我可以Inner Join多次使用吗？
回答by Ganesh_Devlekar
Answer is Yes: You can Use Inner joinyou can create join on Common Columns
答案是肯定的：您可以使用内部联接，您可以在公共列上创建联接
select E.CaseNum, E.FileNum, E.ActivityNum,E.Grade,V.score from Evalulation E
inner join Value V
ON E.CaseNum=V.CaseNum and
E.FileNum=V.FileNum and
E.ActivityNum=V.ActivityNum
Create Table
创建表
Create table MyNewTab(CaseNum int, FileNum int,
ActivityNum int,Grade int,score varchar(100))
Insert values
插入值
Insert into MyNewTab Values(CaseNum, FileNum, ActivityNum,Grade,score)
select E.CaseNum, E.FileNum, E.ActivityNum,E.Grade,V.score from Evalulation E
inner join Value V
ON E.CaseNum=V.CaseNum and
E.FileNum=V.FileNum and
E.ActivityNum=V.ActivityNum
回答by Barett
No, just include the different fields in the "ON" clause of 1 inner join statement:
不，只需在 1 个内部连接语句的“ON”子句中包含不同的字段：
SELECT * from Evalulation e JOIN Value v ON e.CaseNum = v.CaseNum
AND e.FileNum = v.FileNum AND e.ActivityNum = v.ActivityNum
回答by Barett
You should only need to do a single join:
你应该只需要做一个连接：
SELECT e.Grade, v.Score, e.CaseNum, e.FileNum, e.ActivityNum
FROM Evaluation e
INNER JOIN Value v ON e.CaseNum = v.CaseNum AND e.FileNum = v.FileNum AND e.ActivityNum = v.ActivityNum
回答by Jason Colyer
You would basically want something along the lines of:
你基本上会想要一些类似的东西：
SELECT e.*, v.Score
FROM Evaluation e
LEFT JOIN Value v
ON v.CaseNum = e.CaseNum AND
v.FileNum = e.FileNum AND
v.ActivityNum = e.ActivityNum;
回答by zXSwordXz
SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score
FROM Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND E.FileNum = V.FileNum AND E.ActivityNum = V.ActivityNum
