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

我有两张 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

但是由于这在性能方面太可怕了(我正在处理大量数据)，因此我正在寻找一种无需任何循环即可执行此操作的选项

最佳答案

尝试density_rank

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

关于sql - 如何联接两个表，但每行仅使用一次，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45230840/

open

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