求最接近的解:
declare
type test_t is table of vw_money%rowtype index by binary_integer;
res test_t;
l_row binary_integer:=1;
sums number;
cal varchar2(1000);
seq varchar2(1000);
j NUMBER;
lv_result NUMBER;
lv_target NUMBER := 33;
lv_cal VARCHAR2(1000);
lv_seq VARCHAR2(1000);
begin
SELECT *
BULK COLLECT INTO res
FROM vw_money;
--dbms_output.put_line(res.count);
lv_result := NULL;
for i in res.first .. 2**res.last-1 loop
sums:=0;
seq:='';
cal:='';
j:=1;
WHILE j<=res.count AND res(j).pow<=i LOOP
if (bitand(i,res(j).pow) <> 0) then -- 2的幂事先算好了
sums:=sums+res(j).amount;
cal:=cal||res(j).amount||'+';
seq:=seq||res(j).id||',';
IF sums - lv_target >= ABS(lv_result - lv_target) THEN ---- 已经有更接近的答案
EXIT;
END IF;
end if;
j:=j+1;
end loop;
IF lv_result IS NULL OR ABS(sums - lv_target) < ABS(lv_result - lv_target) THEN
lv_result := sums;
lv_cal := cal;
lv_seq := seq;
END IF;
IF lv_result = lv_target THEN
EXIT;
END IF;
end loop;
lv_cal:=substr(lv_cal,1,length(lv_cal)-1);
lv_seq:=substr(lv_seq,1,length(lv_seq)-1);
dbms_output.put_line('结果是:'||lv_cal||'='||lv_result);
dbms_output.put_line('序列是:'||lv_seq);
end;
declare
type test_t is table of vw_money%rowtype index by binary_integer;
res test_t;
l_row binary_integer:=1;
sums number;
cal varchar2(1000);
seq varchar2(1000);
j NUMBER;
lv_result NUMBER;
lv_target NUMBER := 33;
lv_cal VARCHAR2(1000);
lv_seq VARCHAR2(1000);
begin
SELECT *
BULK COLLECT INTO res
FROM vw_money;
--dbms_output.put_line(res.count);
lv_result := NULL;
for i in res.first .. 2**res.last-1 loop
sums:=0;
seq:='';
cal:='';
j:=1;
WHILE j<=res.count AND res(j).pow<=i LOOP
if (bitand(i,res(j).pow) <> 0) then -- 2的幂事先算好了
sums:=sums+res(j).amount;
cal:=cal||res(j).amount||'+';
seq:=seq||res(j).id||',';
IF sums - lv_target >= ABS(lv_result - lv_target) THEN ---- 已经有更接近的答案
EXIT;
END IF;
end if;
j:=j+1;
end loop;
IF lv_result IS NULL OR ABS(sums - lv_target) < ABS(lv_result - lv_target) THEN
lv_result := sums;
lv_cal := cal;
lv_seq := seq;
END IF;
IF lv_result = lv_target THEN
EXIT;
END IF;
end loop;
lv_cal:=substr(lv_cal,1,length(lv_cal)-1);
lv_seq:=substr(lv_seq,1,length(lv_seq)-1);
dbms_output.put_line('结果是:'||lv_cal||'='||lv_result);
dbms_output.put_line('序列是:'||lv_seq);
end;