连接表时，根据查询结果调整 "on" 语句

Question

我有两个表：

• 表_1，列为col_A、col_B、col_C、col_D、col_E
• 表_2，列为col_A、col_B、col_C、col_D、col_F

我想把它们连接到col_A、col_B、col_C、col_D列。

对于表_1中没有以这种方式联接的行(因为它们在表_2中没有匹配项(，我希望仅在列col_a、col_B和col_C上联接它们。

如果Table_1中仍有未联接的行，我希望仅在列col_A、col_B上联接它们。

一旦完成，并且Table_1中仍有行没有联接，我希望只在col_A列上联接它们。

我写了下面的脚本，其中我使用一个新表来获得这个结果。有没有更有效的方法可以做到这一点？最好创建一个视图，而不是一个表？

create table new_table (col_A nvarchar(50) , col_B nvarchar(50) , col_C nvarchar(50) 
, col_D nvarchar(50) , col_E nvarchar(50) , col_F nvarchar(50) )
go
insert into new_table
select Table_1.* , Table_2.col_F 
from Table_1 
inner join Table_2
on Table_1.col_A=Table_2.col_A 
and Table_1.col_B=Table_2.col_B 
and Table_1.col_C=Table_2.col_C 
and Table_1.col_D=Table_2.col_D
go

insert into new_table
select Table_1.* , Table_2.col_F 
from Table_1
inner join Table_2
on Table_1.col_A=Table_2.col_A 
and Table_1.col_B=Table_2.col_B 
and Table_1.col_C=Table_2.col_C
where   concat (Table_1.col_A, Table_1.col_B , Table_1.col_C , Table_1.col_D , Table_1.col_E 
not in (select concat (col_A, col_B , col_C , col_D , col_E) from new_table)
go
insert into new_table
select Table_1.* , Table_2.col_F 
from Table_1
inner join Table_2
on Table_1.col_A=Table_2.col_A 
and Table_1.col_B=Table_2.col_B
where   concat (Table_1.col_A, Table_1.col_B , Table_1.col_C , Table_1.col_D , Table_1.col_E 
not in (select concat (col_A, col_B , col_C , col_D , col_E) from new_table)
go
insert into new_table
select Table_1.* , Table_2.col_F 
from Table_1
inner join Table_2
on Table_1.col_A=Table_2.col_A
where   concat (Table_1.col_A, Table_1.col_B , Table_1.col_C , Table_1.col_D , Table_1.col_E 
not in (select concat (col_A, col_B , col_C , col_D , col_E) from new_table)
go

Answer 1

您可以在colA上加入它们，然后分配一些不同的数字：

WITH cte AS(
SELECT
CASE WHEN t1.D = t2.D THEN 100 ELSE 0 END + 
CASE WHEN t1.C = t2.C THEN 10 ELSE 0 END + 
CASE WHEN t1.B = t2.B THEN 1 ELSE 0 END as whatMatched,
*
FROM
t1 JOIN t2 on t1.A = t2.A
)

现在，如果一行得到111，我们知道所有(ABCD(都匹配，得到0，那么只有a匹配等等

所以我们只能要求一些行：

SELECT * FROM cte WHERE whatmatched IN (111,11,1,0)

最后，如果有倍数(只匹配A可能意味着有重复(，我们可以按降序为它们分配一个行号，只取第一行：

SELECT x.* FROM
(SELECT *, ROW_NUMBER() OVER(ORDER BY whatmatched DESC) rown FROM cte WHERE whatmatched IN (111,11,1,0)) x
WHERE x.rown = 1

如果你适合的话，最好用字母

我们可以评估匹配，只选择A、AB、ABC或ABCD，然后通过查看匹配字符串的LEN强度来选择最具体的一个：

WITH cte AS(
SELECT
'A' +
CASE WHEN t1.B = t2.B THEN 'B' ELSE '' END + 
CASE WHEN t1.C = t2.C THEN 'C' ELSE '' END + 
CASE WHEN t1.D = t2.D THEN 'D' ELSE '' END as whatMatched,
*
FROM
t1 JOIN t2 on t1.A = t2.A
)
SELECT x.* FROM
(SELECT *, ROW_NUMBER() OVER(ORDER BY LEN(whatmatched) DESC) rown FROM cte WHERE whatmatched IN ('A','AB','ABC','ABCD')) x
WHERE x.rown = 1

如果您想要平局(即t1中的一行与t2中的两行匹配，因为它们的a/B/C相同而D不同，请使用DENSE_RANK而不是ROW_NUMBER，这样它们最终会并列第一位