|
程序如下,以下算法仅仅是猜测,无法证明是最优解。
CREATE TABLE guess AS
WITH tmp AS (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=9)
,num AS ( ---- 所有可能的答案,共有C(3,9)=84 种
SELECT REPLACE(SYS_CONNECT_BY_PATH(l,','),',') AS num
FROM tmp
WHERE LEVEL=3
CONNECT BY l>PRIOR L AND LEVEL<=3
)
,attempt AS ( ---- 所有可能的猜测,共有C(4,9)=126 种
SELECT REPLACE(SYS_CONNECT_BY_PATH(l,','),',') AS attempt
FROM tmp
WHERE LEVEL=4
CONNECT BY l>PRIOR l AND LEVEL<=4
)
SELECT attempt.attempt ---- 所有猜测和答案的组合
,num.num
,4-LENGTH(REPLACE(TRANSLATE(attempt.attempt,num.num,'***'),'*')) AS hit ---- 猜对的个数
FROM num,attempt;
CREATE TABLE result (seq number,attempt varchar2(4), num varchar2(3), hit number);
解法说明:
把每种猜测看作一个树节点,猜对个数的不同形成树枝,答案是树叶。目标就是构造一棵树使得高度尽可能的低。
每当提出一种猜测,根据不同的猜对个数形成几个树枝,每个树枝都会筛选出一部分答案,只剩下一个答案就猜对了。
如果答案多于一个则需要根据某种策略选择下一次猜测。
同样的策略使用于其他树枝,这个过程是递归的。
CREATE OR REPLACE PROCEDURE p_guess
AS
PROCEDURE p_try (p_seq IN NUMBER,p_attempt VARCHAR2,p_hit IN NUMBER)
AS
lv_seq NUMBER := p_seq+1;
lv_attempt NUMBER;
BEGIN
BEGIN
-- 决定下一轮要猜什么数
-- 策略是使得答案尽可能分散在不同的树枝,如果过于集中于某个分支,则助长了树的高度
-- 而下一轮问题的树枝则要尽可能的多,这样也有助于降低高度
-- 以上两点就是排序的依据
SELECT attempt
INTO lv_attempt
FROM (SELECT attempt
FROM guess
WHERE attempt NOT IN (SELECT attempt FROM result)
AND (p_seq=0
OR p_seq>0 AND num in (SELECT num
FROM result
WHERE hit=p_hit AND attempt=p_attempt and seq=p_seq
)
)
GROUP BY attempt, hit
ORDER BY MAX(COUNT(*)) OVER(PARTITION BY attempt)
,COUNT(*) OVER(PARTITION BY attempt) DESC
,attempt
)
WHERE ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
INSERT INTO result
SELECT lv_seq
,attempt
,num
,hit
FROM guess
WHERE attempt = lv_attempt
AND (p_seq=0
OR p_seq>0 AND num in (SELECT num
FROM result
WHERE hit=p_hit AND attempt=p_attempt and seq=p_seq
)
);
FOR lv_rec IN (SELECT hit, COUNT(*) CNT
FROM result
WHERE seq=lv_seq
AND attempt = lv_attempt
GROUP BY hit
HAVING COUNT(*)>1
ORDER BY 2
)
LOOP
p_try (lv_seq,lv_attempt,lv_rec.hit);
END LOOP;
END p_try;
BEGIN
DELETE RESULT;
p_try(0,NULL,NULL);
COMMIT;
FOR lv_rec IN (SELECT SEQ
,SUBSTR(SYS_CONNECT_BY_PATH(ATTEMPT||':'||HIT,'->'),3) path
,num
FROM result
WHERE CONNECT_BY_ISLEAF=1
START WITH seq=1
CONNECT BY seq=prior seq+1 AND num=prior num
ORDER BY 2
)
LOOP
DBMS_OUTPUT.PUT_LINE(lv_rec.seq||' '||lv_rec.path||' {'||lv_rec.num||'}');
END LOOP;
END p_guess;
/
SQL> EXEC P_GUESS;
2 1234:0->1257:0 {689}
3 1234:0->1257:1->1258:0 {679}
4 1234:0->1257:1->1258:1->1259:0 {678}
4 1234:0->1257:1->1258:1->1259:1 {789}
4 1234:0->1257:1->1258:1->1259:2 {569}
4 1234:0->1257:1->1258:2->1239:0 {568}
4 1234:0->1257:1->1258:2->1239:1 {589}
4 1234:0->1257:2->1236:0->1238:0 {579}
4 1234:0->1257:2->1236:0->1238:1 {578}
3 1234:0->1257:2->1236:1 {567}
3 1234:1->1358:0->1457:0 {269}
4 1234:1->1358:0->1457:1->1459:0 {267}
4 1234:1->1358:0->1457:1->1459:1 {279}
4 1234:1->1358:0->1457:1->1459:2 {469}
4 1234:1->1358:0->1457:2->1359:0 {467}
4 1234:1->1358:0->1457:2->1359:1 {479}
4 1234:1->1358:1->1467:0->1389:1 {259}
4 1234:1->1358:1->1467:0->1389:2 {289}
4 1234:1->1358:1->1467:1->2356:0 {489}
5 1234:1->1358:1->1467:1->2356:1->1569:0 {278}
5 1234:1->1358:1->1467:1->2356:1->1569:1 {379}
5 1234:1->1358:1->1467:1->2356:1->1569:2 {459}
5 1234:1->1358:1->1467:1->2356:2->1578:0 {369}
5 1234:1->1358:1->1467:1->2356:2->1578:1 {268}
5 1234:1->1358:1->1467:1->2356:2->1578:2 {257}
4 1234:1->1358:1->1467:1->2356:3 {256}
4 1234:1->1358:1->1467:2->4568:0 {179}
5 1234:1->1358:1->1467:2->4568:1->1469:1 {367}
5 1234:1->1358:1->1467:2->4568:1->1469:3 {169}
5 1234:1->1358:1->1467:2->4568:2->1489:1 {457}
5 1234:1->1358:1->1467:2->4568:2->1489:2 {478}
5 1234:1->1358:1->1467:2->4568:3->1478:1 {456}
5 1234:1->1358:1->1467:2->4568:3->1478:2 {468}
3 1234:1->1358:1->1467:3 {167}
4 1234:1->1358:2->1468:0->1367:1 {359}
4 1234:1->1358:2->1468:0->1367:2 {357}
4 1234:1->1358:2->1468:1->1567:0 {389}
5 1234:1->1358:2->1468:1->1567:1->1369:0 {258}
5 1234:1->1358:2->1468:1->1567:1->1369:1 {378}
5 1234:1->1358:2->1468:1->1567:2->1379:1 {356}
5 1234:1->1358:2->1468:1->1567:2->1379:2 {159}
4 1234:1->1358:2->1468:1->1567:3 {157}
5 1234:1->1358:2->1468:2->1378:1->1348:1 {156}
5 1234:1->1358:2->1468:2->1378:1->1348:2 {458}
5 1234:1->1358:2->1468:2->1378:2->1368:2 {189}
5 1234:1->1358:2->1468:2->1378:2->1368:3 {368}
4 1234:1->1358:2->1468:2->1378:3 {178}
3 1234:1->1358:2->1468:3 {168}
3 1234:1->1358:3->1456:1 {358}
3 1234:1->1358:3->1456:2 {158}
4 1234:2->1267:0->1245:1->1248:1 {349}
4 1234:2->1267:0->1245:1->1248:2 {348}
3 1234:2->1267:0->1245:2 {345}
4 1234:2->1267:1->1357:0->1278:1 {249}
4 1234:2->1267:1->1357:0->1278:2 {248}
4 1234:2->1267:1->1357:1->1458:0 {239}
5 1234:2->1267:1->1357:1->1458:1->1349:1 {238}
5 1234:2->1267:1->1357:1->1458:1->1349:2 {346}
5 1234:2->1267:1->1357:1->1458:2->1347:1 {245}
5 1234:2->1267:1->1357:1->1458:2->1347:2 {149}
4 1234:2->1267:1->1357:1->1458:3 {148}
4 1234:2->1267:1->1357:2->1289:0 {347}
5 1234:2->1267:1->1357:2->1289:1->1346:1 {235}
5 1234:2->1267:1->1357:2->1289:1->1346:2 {145}
5 1234:2->1267:1->1357:2->1289:2->1279:1 {138}
5 1234:2->1267:1->1357:2->1289:2->1279:2 {139}
3 1234:2->1267:1->1357:3 {135}
4 1234:2->1267:2->1589:0->1246:1 {237}
5 1234:2->1267:2->1589:0->1246:2->1247:1 {236}
5 1234:2->1267:2->1589:0->1246:2->1247:3 {247}
4 1234:2->1267:2->1589:0->1246:3 {246}
4 1234:2->1267:2->1589:1->1356:1 {147}
5 1234:2->1267:2->1589:1->1356:2->1269:1 {137}
5 1234:2->1267:2->1589:1->1356:2->1269:2 {146}
4 1234:2->1267:2->1589:1->1356:3 {136}
5 1234:2->1267:2->1589:2->1249:2->1268:2 {125}
5 1234:2->1267:2->1589:2->1249:2->1268:3 {128}
4 1234:2->1267:2->1589:2->1249:3 {129}
3 1234:2->1267:3->1237:2 {126}
3 1234:2->1267:3->1237:3 {127}
3 1234:3->1256:1->1345:2 {234}
3 1234:3->1256:1->1345:3 {134}
3 1234:3->1256:2->1235:2 {124}
3 1234:3->1256:2->1235:3 {123}
PL/SQL procedure successfully completed.
[ 本帖最后由 newkid 于 2009-10-7 09:08 编辑 ] |
|