我有两张桌子.一个代表仍然开放的发票(表#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