代码之家 › 专栏 › 技术社区 › Rips

SQL服务器erver:Dynamic Join 条款

join sql-server sql

Rips · 技术社区 · 6 年前

我有两张桌子:

Id extId

表B

Id1 ID2 ID3 id4

查询应首先加入 TableA.extid=TableB.Id1 . TableA.extid=tableB.Id2 等。。在SQLServer中实现这一点的最佳方法是什么?

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

使用多个 left join

select coalese(a1.id, a2.id, a3.id, a4.id) as a_id, b.*
from b left join
     a a1
     on b.id1 = a1.extid left join
     a a2
     on b.id2 = a2.extid and a1.extid is null left join
     a a3
     on b.id3 = a3.extid and a2.extid is null left join
     a a4
     on b.id4 = a4.extid and a3.extid is null 
where a1.extid is not null or a2.extid is not null or a3.extid ia not null or a4.extid is not null;

Thorsten Kettner 6 年前

我把任务理解为:加入ID1上的表。如果查询没有结果,则连接ID2上的表。等等。如果这是正确的,那么:

您可以简单地连接所有ID,然后只保留“最佳”ID上连接的行 TOP 条款:

select top(1) with ties *
from tablea a
join tableb b on a.extid in (b.id1, b.id2, b.id3, b.id4)
order by
  case when a.extid = b.id1 then 1
       when a.extid = b.id2 then 2
       when a.extid = b.id3 then 3
       when a.extid = b.id4 then 4
  end;

如果你的意思是排

select top(1) with ties *
from tablea a
join tableb b on a.extid in (b.id1, b.id2, b.id3, b.id4)
order by 
  row_number() over (
    partition by a.id
    order by 
      case when a.extid = b.id1 then 1
           when a.extid = b.id2 then 2
           when a.extid = b.id3 then 3
           when a.extid = b.id4 then 4
      end);

样本数据:

TableA

ID   | EXTID
-----+------
100  | 1
200  | 2
300  | 3
400  | 4

TableB

ID1 | ID2 | ID3 | ID4
----+-----+-----+----
2   | 3   |     |
2   | 4   |     |
3   | 4   |     |
3   | 2   | 4   | 1

Result for query #1 (all matches on ID1):

ID  | EXTID | ID1 | ID2 | ID3 | ID4
----+-------+-----+-----+-----+----
200 | 2     | 2   | 3   |     |
200 | 2     | 2   | 4   |     |
300 | 3     | 3   | 4   |     |
300 | 3     | 3   | 2   | 4   | 1

Result for query #2 (first matching ID):

ID  | EXTID | ID1 | ID2 | ID3 | ID4
----+-------+-----+-----+-----+----
100 | 1     | 3   | 2   | 4   | 1
200 | 2     | 2   | 3   |     |
200 | 2     | 2   | 4   |     |
300 | 3     | 3   | 4   |     |
300 | 3     | 3   | 2   | 4   | 1
400 | 4     | 2   | 4   |     |
400 | 4     | 3   | 4   |     |

Naim Halai 6 年前

如果已知Id1、Id2、Id3、Id4等的数目,联合可能是一个好的选择。

SELECT ta.Id,
ta.extId,
tb.Id1 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id1

UNION

SELECT ta.Id,
ta.extId,
tb.Id2 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id2

UNION

SELECT ta.Id,
ta.extId,
tb.Id3 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id3

UNION

SELECT ta.Id,
ta.extId,
tb.Id4 AS JoinedID
FROM TableA ta
INNER JOIN TableB tb ON ta.extId = tb.Id4