sql - 如何连接两个表但每行仅使用一次

Question

我有两张 table 。一张代表尚未结清的发票(表#OPEN)，另一张代表可用资金(表#overpay)。两者都有一个USERID列，可以通过该列将它们连接起来:

CREATE TABLE #OVERPAY(OID INT, USERID CHAR(1), Rest INT)
CREATE TABLE #OPEN(IID INT, USERID CHAR(1), Amt INT, OpenROW INT)

表 #OPEN 有一个列 OpenRow，未结金额按该列排序(每个用户)。我想通过以下方式将表 #OVERPAY 中的条目映射到表 #OPEN 中的条目:

• 当 #OVERPAY.Rest >= #OPEN.AMT 时，条目会被映射
• 首先映射#OPEN.OpenRow 中较小的值
• #OVERPAY 中的每个条目只能使用一次
• #OPEN 中的每个条目只能使用一次

该列表中的最后两点让我头疼。

这是一些模拟数据:

OID    USERID   REST
--------------------
1      'A'      10 
2      'A'      15 
3      'F'      5
4      'H'      20 
5      'H'      5

INSERT INTO #OVERPAY(OID, USERID, Rest)
VALUES (1, 'A', 10), (2, 'A', 15), (3, 'F', 5),
       (4, 'H', 20), (5, 'H', 5)

OID    USERID   Amt   OpenRow
-----------------------------
1      'A'      10    1 
2      'A'      10    2
3      'A'      15    3 
4      'F'      5     1
5      'H'      15    1 
6      'H'      10    2
7      'P'      33    1

INSERT INTO #OPEN(IID, USERID, Amt, OpenROW)
VALUES (1, 'A', 10, 1), (2, 'A', 10, 2),
       (3, 'A', 15, 3), (4, 'F', 5, 1),
       (5, 'H', 15, 1), (6, 'H', 10, 2),
       (7, 'P', 33, 1)

期望的结果是:

OID    IID
----------
1      1
2      2
3      4
4      5

我知道如何使用CURSOR来做到这一点:

CREATE TABLE #map (OID INT, IID INT)
CREATE TABLE #usedIID(IID INT)

DECLARE @OID INT, @USERID CHAR(1), @Rest INT

DECLARE ov_cursor CURSOR FOR
    SELECT OID, USERID, REST
    FROM #OVERPAY

OPEN ov_cursor
FETCH NEXT FROM ov_cursor INTO @OID, @USERID, @REST

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @IID INT

    INSERT INTO #map (OID, IID)
    OUTPUT inserted.IID INTO #usedIID (IID)
        SELECT TOP 1 @OID, o.IID
        FROM #OPEN o
        LEFT JOIN #usedIID u ON u.IID = o.IID
        WHERE o.USERID = @USERID AND o.Amt <= @REST AND u.IID IS NULL

    FETCH NEXT FROM ov_cursor INTO @OID, @USERID, @REST
END

CLOSE ov_cursor
DEALLOCATE ov_cursor

但是由于这在性能方面很糟糕(我正在处理大量数据)，我正在寻找一个无需任何循环即可完成此操作的选项

Answer 1

尝试密集等级

SELECT OID, IID
FROM (
    SELECT op.OID, n.IID, OpenRow
      , dense_rank() over(partition by iid order by oid) rnkIid
      , dense_rank() over(partition by oid order by OpenRow) rnkOid
    FROM #OVERPAY op
    JOIN #OPEN n ON op.USERID = n.USERID AND op.Rest >= n.AMT
) t
WHERE rnkIid = rnkOid
ORDER BY OID, IID