如何在SQL中按多列联接两个表?
我有两个表Evalulation
和Value
在两个表中都有四列.但是四个中的三个是相同的.换句话说,它们都具有CaseNum
,FileNum
,ActivityNum
.除了这些列之外,Evaluation
还具有列Grade
,而Value
还具有列Score
.
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, Evaluation
has column Grade
and Value
has column Score
.
我想通过CaseNum
,FileNum
,ActivityNum
将两者合并到一个表中,所以我有一个新的5列表,其中同时包含Value
和Score
.
I want to merge the two into one table by CaseNum
, FileNum
, ActivityNum
so I have a new table of 5 columns with both Value
and Score
in it.
我可以多次使用Inner Join
来执行此操作吗?
Can I use Inner Join
multiple times to do this?
答案是肯定的:您可以使用内部联接 您可以在通用列"上创建联接
Answer is Yes: You can Use Inner join you 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 MyNewTab(CaseNum int, FileNum int,
ActivityNum int,Grade int,score varchar(100))
插入值
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