时间戳

首页 > 解决方案 > T-SQL 嵌套游标 - 所有可能的排列匹配

sql - T-SQL 嵌套游标 - 所有可能的排列匹配

问题描述

我有一个非常具体的问题 - 我正在尝试匹配 vales - 一对多。我对游标相当陌生，因此无法解决我自己的问题。

有点见识——我有两张表——一张是正值，一张是负值。我必须将负数的总和与正数相匹配，并且差异不能超过 +/- 5。示例表如下：

  TABLE1
-----------
200  | abc
125  | abc



  TABLE2
----------
-50  | abc
-50  | abc
-75  | abc
-100 | abc
-125 | abc

我想尝试将负值求和TABLE2，而不管顺序如何，以尝试匹配来自的正值TABLE1 问题是我的光标必须在记录之间移动没有特定的顺序，我使用的是CURSORwithoutSCROLL选项，因为我以前从未使用过它。

我将如何编写代码，该代码将测试所有可能的排列，或者至少直到它得到匹配的点，在这种情况下，记录 1,2 和 4 fromTABLE2将总结为 record fromTABLE1或 records 3 & 5 fromTABLE2将总结为记录 from TABLE1。

甚至可能吗？

另外- 我需要强调的是，这TABLE1将有比我在示例中使用的更多的记录，并且如果我匹配值 1,2 和 4，TABLE2我不能在下一个匹配循环中再次使用这些值来获取下一个记录TABLE1，仅在这种情况下TABLE2可以使用值 5 from 。

到目前为止，这是我必须拥有的代码，我认为它不正确。

DECLARE @idPos as int
DECLARE @zuonrPos as varchar(18)
DECLARE @belnrPos as varchar(10)
DECLARE @dmbtrPos as decimal(15,2)

DECLARE @idNeg as int
DECLARE @zuonrNeg as varchar(18)
DECLARE @belnrNeg as varchar(10)
DECLARE @dmbtrNeg as decimal(15,2)

DECLARE @SumVal as numeric(15,2) = 0

DECLARE @outerLoop as int
DECLARE @innerLoop as int

IF OBJECT_ID('tempdb..#Report') IS NOT NULL DROP TABLE #Report
CREATE TABLE #Report (
    ZUONR varchar(18),
    BELNRPos varchar(10),
    SumPosVal decimal(15,2),
    BELNRNeg varchar(10),
    SumNegVal decimal(15,2)
)

IF OBJECT_ID('tempdb..#OpenItems') IS NOT NULL DROP TABLE #OpenItems
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY MN.ZUONR) AS ID, MN.ZUONR, MN.BELNR, MN.DMBTR, NULL as Marker INTO #OpenItems
FROM dbo.FIS_BELEG MN WITH (NOLOCK)
WHERE MN.ZUONR IS NOT NULL AND MN.HKONT IN ('00123','00122') AND (MN.AUGBL IS NULL OR MN.AUGBL = '') AND MN.DMBTR > 0
ORDER BY MN.ZUONR, MN.DMBTR

IF OBJECT_ID('tempdb..#NegValues') IS NOT NULL DROP TABLE #NegValues
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY MN.ZUONR) AS ID, MN.ZUONR, MN.BELNR, MN.DMBTR, NULL as Marker INTO #NegValues
FROM dbo.FIS_BELEG MN WITH (NOLOCK)
WHERE MN.ZUONR IS NOT NULL AND MN.HKONT IN ('00123','00122') AND (MN.AUGBL IS NULL OR MN.AUGBL = '') AND MN.DMBTR < 0
ORDER BY MN.ZUONR, MN.DMBTR

DECLARE PosCurs CURSOR FOR
    SELECT DISTINCT ID, ZUONR, BELNR, DMBTR FROM #OpenItems
    WHERE Marker IS NULL AND ZUONR = @zuonrPos

OPEN PosCurs
FETCH NEXT FROM PosCurs INTO @idPos, @zuonrPos, @belnrPos, @dmbtrPos
SET @outerLoop = @@FETCH_STATUS
WHILE @outerLoop = 0
    BEGIN
    DECLARE NegCurs CURSOR FOR
        SELECT DISTINCT ID, ZUONR, BELNR, DMBTR FROM #NegValues
        WHERE Marker IS NULL
    OPEN NegCurs
    FETCH NEXT FROM NegCurs INTO @idNeg, @zuonrNeg, @belnrNeg, @dmbtrNeg
    SET @innerLoop = @@FETCH_STATUS
    WHILE @innerLoop = 0 AND (@dmbtrPos BETWEEN (@SumVal - 5) AND (@SumVal + 5)) AND (@SumVal * 0.01) < 5
        BEGIN
            SET @SumVal = @SumVal + ABS(@dmbtrNeg)
            INSERT INTO #Report VALUES (@zuonrPos, @belnrPos, @dmbtrPos, @belnrNeg, @dmbtrNeg)
            UPDATE #OpenItems
                SET Marker = 1 WHERE ZUONR = @zuonrPos AND BELNR = @belnrPos
            UPDATE #NegValues
                SET Marker = 1 WHERE ZUONR = @zuonrNeg AND BELNR = @belnrNeg
            FETCH NEXT FROM NegCurs INTO @idNeg, @zuonrNeg, @belnrNeg, @dmbtrNeg
        END
        CLOSE NegCurs
        DEALLOCATE NegCurs

        SET @SumVal = 0

    FETCH NEXT FROM PosCurs INTO @idPos, @zuonrPos, @belnrPos, @dmbtrPos
    END

CLOSE PosCurs
DEALLOCATE PosCurs

我已经浏览了一段时间的互联网试图找到答案，但是我这样做失败了。有任何想法吗？

标签： sqlsql-serverrecursiondatabase-cursor

解决方案

递归 cte（非高性能）：

declare @table2 table
(
    amount money,
    cola varchar(10)
);

insert into @table2(amount, cola)
values(-50, 'abc'), (-50, 'abc'), (-75, 'abc'), (-100, 'abc'), (-125, 'abc');

declare @table1 table
(
    total money,
    colb varchar(10)
);
insert into @table1(total, colb)
values(200, 'abc');


with o
as
(
    select *, row_number() over(order by amount) as rownum
    from @table2
),
cte as
(
    select amount, colA, rownum, amount as addamount, cast(concat(amount ,',') as varchar(max)) as concatamount
    from o
    union all
    select o.amount, o.colA, o.rownum, cte.addamount+o.amount, cast(concat(cte.concatamount, o.amount, ',') as varchar(max)) 
    from cte
    join o on cte.rownum< o.rownum
)
select *
from cte as c
left join @table1 as t1 on c.addamount = -1*t1.total;

推荐阅读