下面是使用 CTE 和窗口函数的基于集合的解决方案。
The ranked_matches
CTE 为中的每一行分配最接近的匹配排名TableA
以及每行的最接近匹配排名TableB
, 使用index
作为决胜局的价值。
The best_matches
CTE 返回行ranked_matches
两个排名均具有最佳排名(排名值 1)。
最后,外部查询使用LEFT JOIN
from TableA
到 到best_matches
CTE 包括TableA
由于已分配最接近的匹配而未分配最佳匹配的行。
请注意,这不会返回示例结果中指示的索引 3 TableA 行的匹配项。该行的最接近匹配是 TableB 索引 3,相差 83。但是,TableB 行与 TableA 索引 2 行更接近匹配,相差 14,因此它已被分配。如果这不是您想要的,请澄清您的问题。我认为这种技术可以进行相应的调整。
CREATE TABLE dbo.TableA(
[index] int NOT NULL
CONSTRAINT PK_TableA PRIMARY KEY
, value int
);
CREATE TABLE dbo.TableB(
[index] int NOT NULL
CONSTRAINT PK_TableB PRIMARY KEY
, value int
);
INSERT INTO dbo.TableA
( [index], value )
VALUES ( 1, 123 ),
( 2, 245 ),
( 3, 342 ),
( 4, 456 ),
( 5, 608 );
INSERT INTO dbo.TableB
( [index], value )
VALUES ( 1, 152 ),
( 2, 159 ),
( 3, 259 );
WITH
ranked_matches AS (
SELECT
a.[index] AS a_index
, a.value AS a_value
, b.[index] b_index
, b.value AS b_value
, RANK() OVER(PARTITION BY a.[index] ORDER BY ABS(a.Value - b.value), b.[index]) AS a_match_rank
, RANK() OVER(PARTITION BY b.[index] ORDER BY ABS(a.Value - b.value), a.[index]) AS b_match_rank
FROM dbo.TableA AS a
CROSS JOIN dbo.TableB AS b
)
, best_matches AS (
SELECT
a_index
, a_value
, b_index
, b_value
FROM ranked_matches
WHERE
a_match_rank = 1
AND b_match_rank= 1
)
SELECT
TableA.[index] AS a_index
, TableA.value AS a_value
, best_matches.b_index
, best_matches.b_value
FROM dbo.TableA
LEFT JOIN best_matches ON
best_matches.a_index = TableA.[index]
ORDER BY
TableA.[index];
EDIT:
尽管此方法使用 CTE,但未使用递归,因此不限于 32K 递归。不过,从性能角度来看,这里可能还有改进的空间。