|
原帖由 tree_new_bee 于 2011-1-11 22:34 发表 ![]()
一看结果,就知道什么地方该优化了:
一个数的所有排列, 其乘积和必然相等, 因此只要求其中一个就够了。
按上面说的改写了一下, 只取数字排列中从小到大的排列。
newkid给的那个给数字排序的方法发挥大用场了,好几个题目中都用到了。
create or replace procedure P_Q74B is
maxnum integer:=1000000;
icopy varchar2(100);
pathlen integer;
tmp varchar2(100);
pathcopy varchar2(4000);
type t_numtable is table of number index by pls_integer;
type tstringtable is table of varchar2(4000) index by varchar2(100);
fact t_numtable;
path tstringtable;
factsum integer;
factsumorder varchar2(100);
sm integer := 0;
function numorder(n integer) return varchar2 is
begin
return TRANSLATE(n,'0123456789','0')||
TRANSLATE(n,'1023456789','1')||
TRANSLATE(n,'2013456789','2')||
TRANSLATE(n,'3012456789','3')||
TRANSLATE(n,'4012356789','4')||
TRANSLATE(n,'5012346789','5')||
TRANSLATE(n,'6012345789','6')||
TRANSLATE(n,'7012345689','7')||
TRANSLATE(n,'8012345679','8')||
TRANSLATE(n,'9012345678','9');
end;
begin
fact(0):=1;
for i in 1..9 loop
fact(i):= i*fact(i-1);
end loop;
for rc in (with t as (select rownum n,
TRANSLATE(rownum,'0123456789','0')||
TRANSLATE(rownum,'1023456789','1')||
TRANSLATE(rownum,'2013456789','2')||
TRANSLATE(rownum,'3012456789','3')||
TRANSLATE(rownum,'4012356789','4')||
TRANSLATE(rownum,'5012346789','5')||
TRANSLATE(rownum,'6012345789','6')||
TRANSLATE(rownum,'7012345689','7')||
TRANSLATE(rownum,'8012345679','8')||
TRANSLATE(rownum,'9012345678','9') norder
from dual connect by rownum<=maxnum)
select norder, count(*) cnt from t group by norder order by to_number(norder))
loop
if path.exists(rc.norder) then goto CONTINUE; end if;
path(rc.norder):= '/'|| rc.norder ||'/';
icopy :=rc.norder;
while true loop
factsum:= 0;
for j in 1..length(icopy) loop
factsum:= factsum + fact(substr(icopy,j,1));
end loop;
factsumorder:=numorder(factsum);
if instr(path(rc.norder), '/'|| factsumorder ||'/') > 0 then
exit;
end if;
if path.exists(factsumorder) then
path(rc.norder) := path(rc.norder) || substr(path(factsumorder),2);
exit;
end if;
path(rc.norder) := path(rc.norder) || factsumorder || '/';
icopy := factsumorder;
end loop;
pathlen := length(translate(path(rc.norder), '/'||path(rc.norder) ,'/'))-1;
if pathlen = 60 then
dbms_output.put_line('i: ' || rc.norder || ' cnt:' || rc.cnt || ' len: ' || pathlen || ' path:' || path(rc.norder));
sm:=sm+rc.cnt;
--return;
end if;
pathcopy := substr(path(rc.norder),instr(path(rc.norder),'/',2,1));
for k in 1.. pathlen-1 loop
tmp := substr(pathcopy, 2,instr(pathcopy, '/',2)-2);
if tmp is null then exit; end if;
if tmp = factsumorder then exit; end if;
path(tmp):=pathcopy;
pathcopy := substr(pathcopy, instr(pathcopy, '/',2,1));
end loop;
<<CONTINUE>>
null;
end loop;
dbms_output.put_line('Count:' || sm);
end P_Q74B;
i: 0479 cnt:18 len: 60 path:/0479/345679/036789/046789/045889/344566/1146/467/4578/04455/289/002234/36/267/2567/2588/02678/03468/01147/0567/1588/01678/02468/01467/5678/00246/478/34458/04449/233569/333467/0258/03444/79/023679/346689/004467/0158/02444/57/0156/248/03446/577/00012/6/027/0345/115/122/5/012/4/24/26/227/0445/169/013366/1445/
i: 1479 cnt:24 len: 60 path:/1479/345679/036789/046789/045889/344566/1146/467/4578/04455/289/002234/36/267/2567/2588/02678/03468/01147/0567/1588/01678/02468/01467/5678/00246/478/34458/04449/233569/333467/0258/03444/79/023679/346689/004467/0158/02444/57/0156/248/03446/577/00012/6/027/0345/115/122/5/012/4/24/26/227/0445/169/013366/1445/
i: 223479 cnt:360 len: 60 path:/223479/345679/036789/046789/045889/344566/1146/467/4578/04455/289/002234/36/267/2567/2588/02678/03468/01147/0567/1588/01678/02468/01467/5678/00246/478/34458/04449/233569/333467/0258/03444/79/023679/346689/004467/0158/02444/57/0156/248/03446/577/00012/6/027/0345/115/122/5/012/4/24/26/227/0445/169/013366/1445/
Count:402
PL/SQL procedure successfully completed
Executed in 28.422 seconds
SQL>
快多了。 实际上28秒里有20多秒都花在了那个SQL语句上。
那个语句改成拼数字串的方法应该会高效的多, 可是涉及到带前导0,以及带重复数字的排列的数目,好烦, 先扔那里吧。
[ 本帖最后由 tree_new_bee 于 2011-1-13 15:42 编辑 ] |
|