具有条件优先级的 SQL 条件连接，如 CASE 语句中所示

Question

如果不满足第一个条件，如何在 (1( 完整密钥或 (2( 部分密钥上有条件地加入。假设 id 由 7 个字符组成。

select 
a.id,
coalesce (t.somevalue, t2.somevalue)
from a
left join t
on a.id=t.id
left join t as t2
on left(a.id,6)=left(t2.id,6)

以上连接可以一次性完成吗？像这样的东西，但保持全键加入的优先级而不是部分键连接？

left join t
on 
a.id=t.id OR left(a.id,6)=left(t2.id,6)

我想知道是否有类似的东西

left join t
on 
case when condition 1
case when condition 2

下面是示例数据和预期结果。

+---------+
|  a.id   |
+---------+
| aaaaa0A |
| aaaaa0B |
| aaaaa1A |
| aaaaa2A |
| aaaaa3B |
+---------+
+---------+
| t.id    |
+---------+
| aaaaa0C |
| aaaaa1B |
| aaaaa1A |
| aaaaa2B |
| aaaaa3A |
+---------+
+---------+---------+--------------+
| a.id    | t.id    | condition no |
+---------+---------+--------------+
| aaaaa0A | aaaaa0C | 2            |
| aaaaa0B | aaaaa0C | 2            |
| aaaaa1A | aaaaa1A | 1            |
| aaaaa2A | aaaaa2B | 2            |
| aaaaa3B | aaaaa3A | 2            |
+---------+---------+--------------+

Answer 1

我想你想要outer apply：

select a.id, t.somevalue
from a outer apply
(select top (1) t.*
from t
where left(a.id, 6) = left(t.id, 6)
order by (case when a.id = t.id then 1 else 2 end)  -- put full matches first
) t;

Answer 2

您可以在第一个连接不匹配的情况下执行t的第二个LEFT连接：

select 
a.id,
coalesce(t1.id, t2.id) id,
case 
when t1.id is not null then 1
when t2.id is not null then 2
else 0
end [condition no] 
from a
left join t as t1 on a.id = t1.id
left join t as t2 on t1.id is null and left(a.id, 6) = left(t2.id, 6)

请参阅演示。
结果：

> id      | id      | condition no
> :------ | :------ | -----------:
> aaaaa0A | aaaaa0C |            2
> aaaaa0B | aaaaa0C |            2
> aaaaa1A | aaaaa1A |            1
> aaaaa2A | aaaaa2B |            2
> aaaaa3B | aaaaa3A |            2