您可以使用以下递归CTE实现所需的输出

WITH 
DATA AS
(
  SELECT * FROM #A A1 INNER JOIN #B B1 ON A1.UpperLimit >= B1.Amount
),
MA AS
(
  SELECT MIN(UpperLimit) AS MinLimit, MAX(UpperLimit) AS MaxLimit FROM #A
),
RESULT AS 
(
  -- Get the first record corresponding with maximum upper limit  
  SELECT * 
  FROM DATA D1
  WHERE NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount)
        AND D1.UpperLimit = (SELECT MaxLimit FROM MA)
  
  -- Recursive get remain record corresponding with other upper limit 
  UNION ALL      
  SELECT D1.* 
  FROM RESULT R1 INNER JOIN DATA D1 
       ON (R1.UpperLimit > D1.UpperLimit AND R1.Id != D1.Id) 
  WHERE D1.UpperLimit >= (SELECT MinLimit FROM MA)
       AND NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount AND D2.Id != R1.Id)   
)

SELECT DISTINCT * FROM RESULT ORDER BY UpperLimit DESC;

演示：https://dbfiddle.uk/Y-m0K6Mk

可能有点长，但希望足够清楚。

with a as
    (select -- order and number rows in table A in some way
        row_number() over (order by UpperLimit) as RnA,
        *
        from #a),
b as
    (select -- order and number rows in table B in the same way
        row_number() over (order by Amount) as RnB,
        *
        from #b),
m as
    (select -- get and number all possible pairs of values from both tables considering the restriction
        row_number() over (order by a.UpperLimit desc, b.Amount desc) as RnM,
        *
        from a
            join b on
                b.Amount < a.UpperLimit),
r as
    (select -- use recursion to get all possible combinations of the value pairs with metrics of interest for comparison
        convert(varchar(max), RnA) as ListA,
        convert(varchar(max), RnB) as ListB,
        RnA,
        RnB,
        1 as CountB,
        convert(int, Amount) as SumB
        from m
        where RnM = 1
    union all
    select
        r.ListA + ' ' + convert(varchar(max), m.RnA),
        r.ListB + ' ' + convert(varchar(max), m.RnB),
        m.RnA,
        m.RnB,
        r.CountB + 1,
        r.SumB + convert(int, m.Amount)
        from m
            join r on
                m.RnA < r.RnA and
                m.RnB < r.RnB),
e as
    (select top(1) -- select combinations of interest using metrics
        ListA,
        ListB
        from r 
        order by CountB desc, SumB desc),
ea as
    (select -- turn id list into table for table A
        ea.Rn,
        ea.Value
        from e
            cross apply(select row_number() over (order by (select null)) as Rn, Value from string_split(e.ListA, ' ')) as ea),
eb as
    (select -- turn id list into table for table B
        eb.Rn,
        eb.Value
        from e
            cross apply(select row_number() over (order by (select null)) as Rn, Value from string_split(e.ListB, ' ')) as eb)
select -- get output table with actual values from the original tables
    a.UpperLimit,
    b.Amount,
    b.Id
    from ea
        join eb on
            ea.Rn = eb.Rn
        join a on
            ea.Value = a.RnA
        join b on
            eb.Value = b.RnB;

为此，可以将APPLY与TOP 1一起使用，外层表中的每一行只能从APPLY中获取一行。

SELECT
  *
FROM #A a
OUTER APPLY (
    SELECT TOP (1) *
    FROM #B b
    WHERE b.Amount < a.UpperLimit
) b;

要模拟内连接（而不是左连接），请使用CROSS APPLY。

此查询返回的结果非常接近所需结果。

WITH CTE AS (SELECT B.*,
                    ROW_NUMBER() OVER (PARTITION BY B.Value ORDER BY B.Value DESC) AS RowNum
             FROM #B B),
     cc as (SELECT A.Limit, CTE.*
            FROM #A A
                     LEFT JOIN CTE ON CTE.Value < A.Limit AND CTE.RowNum = 1),
     cc2 as (select *, MAX(Value) OVER ( PARTITION BY cc.Limit) as l1 from cc)
select Limit, ID, Value
from cc2
where Value = l1

此查询使用3个公用表表达式。第一个使用ROW_NUMBER（）函数和PARTITION BY子句对表B进行排序，第二个使用给定的条件将表A与表B进行JOIN，第三个筛选表A上处于Limit中的记录，并仅使用一次Limit。