sql – 如何连接两个表但只使用每一行一次

我有两张桌子.一个代表仍然开放的发票(表#OPEN),另一个代表可用货币(表#overpay).两者都有一个USERID列,可以通过它们加入它们：

CREATE TABLE #OVERPAY(OID INT,USERID CHAR(1),Rest INT)
CREATE TABLE #OPEN(IID INT,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,15),(3,'F',5),(4,'H',20),(5,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,Amt,OpenROW)
VALUES (1,10,1),2),15,3),5,(6,(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,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,@REST
END

CLOSE ov_cursor
DEALLOCATE ov_cursor

但是因为这在性能方面很糟糕(我正在处理大量数据)我正在寻找一个没有任何循环的选项

解决方法

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

（编辑：岳阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!