还是井字棋
http://blog.itpub.net/29254281/viewspace-1852817/
今天和王工相互印证之下,发现原来的SQL还是有点问题.
1.原来的SQL没有计算平局的情况
2.我对于棋盘的理解有错误.
下面这个棋盘(Board)
其实应该是 O-X-OOXXX
居然把题目理解错了...
3.还有一个bug. 假如先手和后手走完了整个棋局,那么先手下了五个子,而后手仅仅下了四个子.
所以原来SQL的这个部分,标红加粗部分应该删除.
其实应该是
所以经过修正之后的SQL,应该如下:
用时:309s
结果:
select winner,count(*) from t1 group by winner;
X 131184
- 46080
O 77904
这个结果和王工计算的结果一致.应该是正确的.
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.idO,
其实应该是
- 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://img.blog.itpub.net/blog/attachment/201512/4/29254281_1449233053CRH4.png?x-oss-process=style/bb)
这个结果和王工计算的结果一致.应该是正确的.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1853692/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1853692/