当列包含两列的组合时进行内部联接

Question

我知道标题很糟糕，只是不知道还能用什么。

我有Employee表，它看起来像这样：

EmpId    EmpNum   Last
ABC      130      Smith
XVC      210      Johnson
ZAL      105      William
GFL      221      Miller

CCD_ 2将加入到CCD_ 3。桌子看起来像这样。EmpId_Num列可以包括EmpId或EmpNum:

EmpId_Num    First
221          Mary
XVC          James
GFL          Alicia

由于empnum和empid永远不可能相同，因此解决方案是使用两个cte，然后对这两个结果进行union，如下所示：

;with cte as
(
select First, Last from Employee inner join EmpUpload on
employee.empnum = empupload.empid_num
),
cte2 as 
(
select First, Last from Employee inner join EmpUpload on
employee.empid = empupload.empid_num
)
select first,last from cte union 
select first,last from cte2

有较短的路吗？

Answer 1

试试这个，你也可以在Joins上使用OR运算符！

select First, Last 
from Employee 
inner join EmpUpload on employee.empnum = empupload.empid_num
OR employee.empid = empupload.empid_num