将数字与数字子集的总和进行比较

反弹过去

我正在寻找您的专家的指导，以找出一种将数字与数字子集的总和进行比较的方法
DECLARE
L_NUM_TO_COMPARE NUMBER := 0;
L_NUM_SUBSET    NUMBER := 0;
BEGIN
FOR MAIN_REC IN (
         SELECT 1 ID, 25150  ASSIGN_AMT FROM DUAL
      UNION ALL
         SELECT 2 ID, 19800  ASSIGN_AMT FROM DUAL
      UNION ALL
         SELECT 3 ID, 27511  ASSIGN_AMT FROM DUAL
) LOOP
      L_NUM_TO_COMPARE := MAIN_REC.ASSIGN_AMT;
      DBMS_OUTPUT.PUT_LINE( L_NUM_TO_COMPARE);
      FOR C IN (
                  SELECT 1  ID, 7120  WORK_AMT FROM DUAL
         UNION ALL SELECT 2  ID, 8150  WORK_AMT FROM DUAL
         UNION ALL SELECT 3  ID, 8255  WORK_AMT FROM DUAL
         UNION ALL SELECT 4  ID, 9051  WORK_AMT FROM DUAL
         UNION ALL SELECT 5  ID, 1220  WORK_AMT FROM DUAL
         UNION ALL SELECT 6  ID, 12515 WORK_AMT FROM DUAL
         UNION ALL SELECT 7  ID, 13555 WORK_AMT FROM DUAL
         UNION ALL SELECT 8  ID, 5221  WORK_AMT FROM DUAL
         UNION ALL SELECT 9  ID, 812 WORK_AMT FROM DUAL
         UNION ALL SELECT 10 ID, 6562  WORK_AMT FROM DUAL
                  ORDER BY 2 DESC
      ) LOOP
         L_NUM_SUBSET := NVL(L_NUM_SUBSET,0) + C.WORK_AMT;
         DBMS_OUTPUT.PUT_LINE( L_NUM_SUBSET);
         /*
            I NEED TO PUT SOME LOGIC HOW CAN I FIND NEAREST SUM OF SUBSET
         */
         IF MAIN_REC.ASSIGN_AMT = L_NUM_SUBSET THEN
            DBMS_OUTPUT.PUT_LINE( L_NUM_SUBSET);
         END IF;
      END LOOP;
END LOOP;
END;
我一直在寻找这个论坛，发现一个问题，子组数字的总和
这几乎是我的相同要求，我所需要的可以指出一点，如何在我拥有的PL / SQL中做到这一点（Oracle DB 11g R2）

解决方案:


            提出问题的方式是，我假设您并不是真正在解决子集和问题，而是一个比较简单的问题，您想将数字与非常有限的子集进行比较，即子集的排序WORK_AMT不断提升，没有差距。
简化的问题
可以单独使用Oracle SQL来解决：
WITH
ASSIGN(ID, ASSIGN_AMT) AS (
               SELECT 1, 25150 FROM DUAL
      UNION ALL SELECT 2, 19800 FROM DUAL
      UNION ALL SELECT 3, 27511 FROM DUAL
),
VALS (ID, WORK_AMT) AS (
               SELECT 1 , 7120  FROM DUAL
      UNION ALL SELECT 2 , 8150  FROM DUAL
      UNION ALL SELECT 3 , 8255  FROM DUAL
      UNION ALL SELECT 4 , 9051  FROM DUAL
      UNION ALL SELECT 5 , 1220  FROM DUAL
      UNION ALL SELECT 6 , 12515 FROM DUAL
      UNION ALL SELECT 7 , 13555 FROM DUAL
      UNION ALL SELECT 8 , 5221  FROM DUAL
      UNION ALL SELECT 9 , 812 FROM DUAL
      UNION ALL SELECT 10, 6562  FROM DUAL
),
SUMS (ID, WORK_AMT, SUBSET_SUM) AS (
      SELECT VALS.*, SUM (WORK_AMT) OVER (ORDER BY ID)
      FROM VALS
)
SELECT
ASSIGN.ID,
ASSIGN.ASSIGN_AMT,
MIN (SUBSET_SUM) KEEP (
      DENSE_RANK FIRST
      ORDER BY ABS (ASSIGN_AMT - SUBSET_SUM)
) AS CLOSEST_SUM
FROM
ASSIGN
CROSS JOIN
SUMS
GROUP BY
ASSIGN.ID, ASSIGN.ASSIGN_AMT
以上收益：
ID  ASSIGN_AMT  CLOSEST_SUM
---------------------------
1 25150    29085
2 19800    20935
3 27511    29085
实际子集总和问题
请注意，此问题在时间和空间上具有指数级的复杂性。对于WORK表中的少量值，只能合理地解决它！
WITH
ASSIGN (ID, ASSIGN_AMT) AS (
               SELECT 1, 25150 FROM DUAL
      UNION ALL SELECT 2, 19800 FROM DUAL
      UNION ALL SELECT 3, 27511 FROM DUAL
),
WORK (ID, WORK_AMT) AS (
               SELECT 1 , 7120  FROM DUAL
      UNION ALL SELECT 2 , 8150  FROM DUAL
      UNION ALL SELECT 3 , 8255  FROM DUAL
      UNION ALL SELECT 4 , 9051  FROM DUAL
      UNION ALL SELECT 5 , 1220  FROM DUAL
      UNION ALL SELECT 6 , 12515 FROM DUAL
      UNION ALL SELECT 7 , 13555 FROM DUAL
      UNION ALL SELECT 8 , 5221  FROM DUAL
      UNION ALL SELECT 9 , 812 FROM DUAL
      UNION ALL SELECT 10, 6562  FROM DUAL
),
SUMS (SUBSET_SUM, MAX_ID) AS (
      SELECT WORK_AMT, ID FROM WORK
      UNION ALL
      SELECT WORK_AMT + SUBSET_SUM, GREATEST (MAX_ID, WORK.ID)
      FROM SUMS JOIN WORK
      ON SUMS.MAX_ID 现在产生：
ID  ASSIGN_AMT  CLOSEST_SUM
---------------------------
1 25150    25133
2 19800    19768
3 27511    27488

将数字与数字子集的总和进行比较

反弹过去 LV1