如何实现两表关联且每个B表记录仅被使用一次？

现有两个临时表：

CREATE TABLE #A (UpperLimit NUMERIC(4))
CREATE TABLE #B (Id NUMERIC(4), Amount NUMERIC(4))

INSERT INTO #A VALUES 
    (1000), (2000), (3000)
INSERT INTO #B VALUES 
    (1, 3100), 
    (2, 1900), 
    (3, 1800), 
    (4, 1700), 
    (5, 900), 
    (6, 800)

需要将#A与#B按B.Amount < A.UpperLimit关联，且#B中的每条记录只能被使用一次，期望输出如下：

要求避免遍历删除的编程式方案，采用递归CTE、分区等纯查询方式实现。

通过递归CTE按UpperLimit从小到大处理，每次为当前UpperLimit分配未被使用的B记录：

WITH RankedA AS (
    -- 为#A按UpperLimit升序排序，确定处理顺序
    SELECT 
        UpperLimit,
        ROW_NUMBER() OVER (ORDER BY UpperLimit) AS A_Row
    FROM #A
),
RankedB AS (
    -- 为#B按Amount升序排序，优先匹配更小的UpperLimit
    SELECT 
        Id,
        Amount,
        ROW_NUMBER() OVER (ORDER BY Amount) AS B_Row
    FROM #B
    WHERE Amount < (SELECT MAX(UpperLimit) FROM #A)
),
RecursiveMatch AS (
    -- 初始步骤：处理第一个UpperLimit，匹配所有符合条件的B记录
    SELECT 
        a.UpperLimit,
        b.Id,
        b.Amount,
        b.B_Row,
        a.A_Row
    FROM RankedA a
    JOIN RankedB b ON b.Amount < a.UpperLimit
    WHERE a.A_Row = 1

    UNION ALL

    -- 递归步骤：处理下一个UpperLimit，匹配未被之前分配的B记录
    SELECT 
        a.UpperLimit,
        b.Id,
        b.Amount,
        b.B_Row,
        a.A_Row
    FROM RankedA a
    JOIN RankedB b ON b.Amount < a.UpperLimit
    JOIN RecursiveMatch rm ON a.A_Row = rm.A_Row + 1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM RecursiveMatch rm_prev 
        WHERE rm_prev.B_Row = b.B_Row
    )
)
-- 输出所有UpperLimit及其匹配记录，无匹配则显示NULL
SELECT 
    a.UpperLimit,
    rm.Id,
    rm.Amount
FROM RankedA a
LEFT JOIN RecursiveMatch rm ON a.A_Row = rm.A_Row
ORDER BY a.UpperLimit, rm.Amount;

先生成所有可能的匹配对，再为每个B记录分配优先级最高（最小）的UpperLimit，最后聚合输出：

WITH AllPossibleMatches AS (
    -- 生成所有符合条件的匹配对，并为每个B记录按UpperLimit升序标记优先级
    SELECT 
        a.UpperLimit,
        b.Id,
        b.Amount,
        ROW_NUMBER() OVER (PARTITION BY b.Id ORDER BY a.UpperLimit) AS MatchPriority
    FROM #A a
    JOIN #B b ON b.Amount < a.UpperLimit
),
AssignedMatches AS (
    -- 保留每个B记录的最优匹配（优先级=1）
    SELECT 
        UpperLimit,
        Id,
        Amount
    FROM AllPossibleMatches
    WHERE MatchPriority = 1
)
-- 输出结果，关联所有UpperLimit
SELECT 
    a.UpperLimit,
    am.Id,
    am.Amount
FROM #A a
LEFT JOIN AssignedMatches am ON a.UpperLimit = am.UpperLimit
ORDER BY a.UpperLimit, am.Amount;

内容的提问来源于stack exchange，提问作者Smudge202