还是井字棋
http://blog.itpub.net/29254281/viewspace-1852817/
今天和王工相互印证之下,发现原来的SQL还是有点问题.
1.原来的SQL没有计算平局的情况
2.我对于棋盘的理解有错误.
下面这个棋盘(Board)
MOVES=3175968,
BOARD=XOXOXOX,
WINNER=X
其实应该是O-X-OOXXX
居然把题目理解错了...
3.还有一个bug.假如先手和后手走完了整个棋局,那么先手下了五个子,而后手仅仅下了四个子.
所以原来SQL的这个部分,标红加粗部分应该删除.
-
select n1.id||n2.id||n3.id||n4.id||n5.id||n6.id||n7.id||n8.id||n9.id moves,
-
n1.id||n3.id||n5.id||n7.id||n9.id X,
-
n2.id||n4.id||n6.id||n8.id
||n9.id O,
其实应该是
-
select n1.id||n2.id||n3.id||n4.id||n5.id||n6.id||n7.id||n8.id||n9.id moves,
-
n1.id||n3.id||n5.id||n7.id||n9.id X,
-
n2.id||n4.id||n6.id||n8.id O,
所以经过修正之后的SQL,应该如下:
-
create table t1 as
-
with nums as (
-
select level id from dual connect by level<=9
-
),
-
MOVES as(
-
select n1.id||n2.id||n3.id||n4.id||n5.id||n6.id||n7.id||n8.id||n9.id moves,
-
n1.id||n3.id||n5.id||n7.id||n9.id X,
-
n2.id||n4.id||n6.id||n8.id O
-
from
-
nums n1,
-
nums n2,
-
nums n3,
-
nums n4,
-
nums n5,
-
nums n6,
-
nums n7,
-
nums n8,
-
nums n9
-
where
-
(n1.id!=n2.id and n1.id!=n3.id and n1.id!=n4.id and n1.id!=n5.id and n1.id!=n6.id and n1.id!=n7.id and n1.id!=n8.id and n1.id!=n9.id) and
-
(n2.id!=n1.id and n2.id!=n3.id and n2.id!=n4.id and n2.id!=n5.id and n2.id!=n6.id and n2.id!=n7.id and n2.id!=n8.id and n2.id!=n9.id) and
-
(n3.id!=n2.id and n3.id!=n1.id and n3.id!=n4.id and n3.id!=n5.id and n3.id!=n6.id and n3.id!=n7.id and n3.id!=n8.id and n3.id!=n9.id) and
-
(n4.id!=n2.id and n4.id!=n3.id and n4.id!=n1.id and n4.id!=n5.id and n4.id!=n6.id and n4.id!=n7.id and n4.id!=n8.id and n4.id!=n9.id) and
-
(n5.id!=n2.id and n5.id!=n3.id and n5.id!=n4.id and n5.id!=n1.id and n5.id!=n6.id and n5.id!=n7.id and n5.id!=n8.id and n5.id!=n9.id) and
-
(n6.id!=n2.id and n6.id!=n3.id and n6.id!=n4.id and n6.id!=n5.id and n6.id!=n1.id and n6.id!=n7.id and n6.id!=n8.id and n6.id!=n9.id) and
-
(n7.id!=n2.id and n7.id!=n3.id and n7.id!=n4.id and n7.id!=n5.id and n7.id!=n6.id and n7.id!=n1.id and n7.id!=n8.id and n7.id!=n9.id) and
-
(n8.id!=n1.id and n8.id!=n3.id and n8.id!=n4.id and n8.id!=n5.id and n8.id!=n6.id and n8.id!=n7.id and n8.id!=n2.id and n8.id!=n9.id) and
-
(n9.id!=n2.id and n9.id!=n3.id and n9.id!=n4.id and n9.id!=n5.id and n9.id!=n6.id and n9.id!=n7.id and n9.id!=n8.id and n9.id!=n1.id)
-
),
-
v1 as (
-
select m.*,
-
translate(m.X,‘123456789‘,‘1__4__7__‘) xc1,
-
translate(m.X,‘123456789‘,‘_2__5__8_‘) xc2,
-
translate(m.X,‘123456789‘,‘__3__6__9‘) xc3,
-
translate(m.X,‘123456789‘,‘1___5___9‘) xc4,
-
translate(m.X,‘123456789‘,‘__3_5_7__‘) xc5,
-
translate(m.X,‘123456789‘,‘123______‘) xc6,
-
translate(m.X,‘123456789‘,‘___456___‘) xc7,
-
translate(m.X,‘123456789‘,‘______789‘) xc8,
-
translate(m.O,‘123456789‘,‘1__4__7__‘) oc1,
-
translate(m.O,‘123456789‘,‘_2__5__8_‘) oc2,
-
translate(m.O,‘123456789‘,‘__3__6__9‘) oc3,
-
translate(m.O,‘123456789‘,‘1___5___9‘) oc4,
-
translate(m.O,‘123456789‘,‘__3_5_7__‘) oc5,
-
translate(m.O,‘123456789‘,‘123______‘) oc6,
-
translate(m.O,‘123456789‘,‘___456___‘) oc7,
-
translate(m.O,‘123456789‘,‘______789‘) oc8
-
from moves m
-
),
-
score as (
-
select
-
v1.*,
-
least(
-
decode(regexp_instr(xc1,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc1,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc2,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc2,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc3,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc3,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc4,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc4,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc5,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc5,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc6,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc6,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc7,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc7,‘[1-9]‘,1,3)),
-
decode(regexp_instr(xc8,‘[1-9]‘,1,3) ,0,999,regexp_instr(xc8,‘[1-9]‘,1,3))
-
-
) xscore,
-
least(
-
decode(regexp_instr(oc1,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc1,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc2,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc2,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc3,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc3,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc4,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc4,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc5,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc5,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc6,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc6,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc7,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc7,‘[1-9]‘,1,3)),
-
decode(regexp_instr(oc8,‘[1-9]‘,1,3) ,0,999,regexp_instr(oc8,‘[1-9]‘,1,3))
-
) oscore
-
from v1
-
)
-
select distinct
-
case when xscore<=oscore then substr(score.moves,0,xscore*2-1) else substr(score.moves,0,2*oscore) end moves,
-
regexp_replace(
-
regexp_replace(
-
regexp_replace(‘123456789‘,‘[‘||case when xscore<=oscore then substr(x,0,xscore) else substr(x,0,oscore) end||‘]‘,‘X‘)
-
,‘[‘||case when xscore<=oscore then substr(o,0,xscore-1) else substr(o,0,oscore) end||‘]‘,‘O‘),‘[1-9]‘,‘-‘) board,
-
case when xscore=oscore and oscore=999 then ‘-‘ when xscore<=oscore then ‘X‘ else ‘O‘ end winner
-
from score;
用时:309s
结果:
select winner,count(*) from t1 group by winner;
X 131184
- 46080
O 77904
这个结果和王工计算的结果一致.应该是正确的.
井字棋解法(补充)
原文：http://blog.itpub.net/29254281/viewspace-1853692/
