我有两张桌子.一个代表仍然开放的发票(表#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
但是因为这在性能方面很糟糕(我正在处理大量数据)我正在寻找一个没有任何循环的选项
解决方法
尝试dense_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
