|
本帖最后由 newkid 于 2013-12-4 06:39 编辑
闲着也是闲着,丑陋法改出来了,不比MODEL(我自己写的MODEL)慢:
WITH cells AS ( ------- 构造所有单元格及其坐标
SELECT LEVEL cell_id -------- 从左上角开始,从左到右从上到下从1开始编号
,CEIL(LEVEL/:v_width) r -------- 行号,1~v_height
,MOD(LEVEL-1,:v_width)+1 c -------- 列号,1~v_width
,SUBSTR(:v_str,LEVEL,1) cnt -------- 该单元格周围的地雷数目
FROM DUAL CONNECT BY LEVEL<=:v_width*:v_height
)
,cells2 AS (
SELECT cell_id,r,c,DECODE(cnt,' ',0,TO_NUMBER(cnt)) AS cnt
------ 如果每个方位的邻居不存在,比如边界上,把该邻居的id设为大数999999, 这样SUBSTR不报错只是返回NULL
,CASE WHEN r>1 AND c>1 THEN cell_id-:v_width-1 ELSE 999999 END AS n1 ---- 左上方邻居的id
,CASE WHEN r>1 THEN cell_id-:v_width ELSE 999999 END AS n2 ---- 正上方邻居的id
,CASE WHEN r>1 AND c<:v_width THEN cell_id-:v_width+1 ELSE 999999 END AS n3 ---- 右上方邻居的id
,CASE WHEN c>1 THEN cell_id-1 ELSE 999999 END AS n4 ---- 左边邻居的id
,CASE WHEN c<:v_width THEN cell_id+1 ELSE 999999 END AS n5 ---- 右边邻居的id
,CASE WHEN r<:v_height AND c>1 THEN cell_id+:v_width-1 ELSE 999999 END AS n6 ---- 左下方邻居的id
,CASE WHEN r<:v_height THEN cell_id+:v_width ELSE 999999 END AS n7 ---- 正下方邻居的id
,CASE WHEN r<:v_height AND c<:v_width THEN cell_id+:v_width+1 ELSE 999999 END AS n8 ---- 右下方邻居的id
------- 用CASE计算周围的空位数,比较难看的写法
,CASE WHEN cnt=' ' THEN 0
ELSE CASE WHEN r>1 THEN
CASE WHEN c>1 THEN DECODE(SUBSTR(:v_str,cell_id-:v_width-1,1),' ',1,0) ELSE 0 END
+DECODE(SUBSTR(:v_str,cell_id-:v_width,1),' ',1,0)
+CASE WHEN c<:v_width THEN DECODE(SUBSTR(:v_str,cell_id-:v_width+1,1),' ',1,0) ELSE 0 END
ELSE 0
END
+CASE WHEN c>1 THEN DECODE(SUBSTR(:v_str,cell_id-1,1),' ',1,0) ELSE 0 END
+CASE WHEN c<:v_width THEN DECODE(SUBSTR(:v_str,cell_id+1,1),' ',1,0) ELSE 0 END
+CASE WHEN r<:v_height THEN
CASE WHEN c>1 THEN DECODE(SUBSTR(:v_str,cell_id+:v_width-1,1),' ',1,0) ELSE 0 END
+DECODE(SUBSTR(:v_str,cell_id+:v_width,1),' ',1,0)
+CASE WHEN c<:v_width THEN DECODE(SUBSTR(:v_str,cell_id+:v_width+1,1),' ',1,0) ELSE 0 END
ELSE 0
END
END AS empty_cnt
from cells c1
)
,empty_cells AS (
SELECT --------- 找出所有空位及其邻居位置, 空位是未标数字的单元格,可能有地雷也可能没有
cc.*
,CASE WHEN n1=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n1,1))),0) END AS cnt1
,CASE WHEN n2=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n2,1))),0) END AS cnt2
,CASE WHEN n3=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n3,1))),0) END AS cnt3
,CASE WHEN n4=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n4,1))),0) END AS cnt4
,CASE WHEN n5=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n5,1))),0) END AS cnt5
,CASE WHEN n6=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n6,1))),0) END AS cnt6
,CASE WHEN n7=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n7,1))),0) END AS cnt7
,CASE WHEN n8=999999 THEN TO_NUMBER(NULL) ELSE NVL(TO_NUMBER(TRIM(SUBSTR(:v_str,n8,1))),0) END AS cnt8
,ROWNUM empty_id ---------- 按顺序为空位取编号,递归的时候每层编号递增,按序猜测每个空位
FROM (SELECT * FROM cells2
WHERE cnt=0 ------- 空位
ORDER BY CASE WHEN cell_id<=2*:v_width THEN c ELSE 9999 END -------------- 霹雳火思路
,CASE WHEN cell_id<=2*:v_width THEN r ELSE 9999 END
,cell_id
) cc
)
,guess AS (SELECT 0 AS mine FROM DUAL UNION ALL SELECT 1 FROM DUAL)
,t(cnt_str ---- 所有格子的计数器, 合并为一个字符串。当猜测为地雷时,就把周围8个计数器都减1
,m_cnt ---- 到目前为止猜测为地雷的总数, 达到:V_CNT时就结束
,empty_id ---- 目前要猜测哪个空位
,res ---- 最后输出的结果。每猜出一个雷就拼一个 * 进去
,empty_cnt_str ---- 每个格子周围的空位计数器,即未猜测的格子数。每结束一个空位的猜测,周围的8个计数器都减1
) AS (
SELECT CAST(:v_str AS VARCHAR2(1000)) -------- 计数器初始化
,0 ------- 未猜测之前总雷数为0
,1 ------- 从第一个空位开始遍历
,CAST(:v_str AS VARCHAR2(1000)) -------- 输出初始为原分布,在递归过程中嵌入地雷
,CAST(LISTAGG(CASE WHEN cnt>0 THEN TO_CHAR(empty_cnt) ELSE ' ' END) ---- 把所有空位计数器合并为一个字符串, 标有数字的才计数
WITHIN GROUP (ORDER BY cell_id) AS VARCHAR2(1000))
FROM cells2
WHERE NOT EXISTS (SELECT 1 FROM cells2 WHERE cnt>empty_cnt) ---如果数字比周围的空位还多,说明题目有错,显然无解
UNION ALL
SELECT ----- 开始更新地雷计数器
CASE WHEN guess.mine=0 THEN cnt_str---- 如果当前猜无雷,则计数器保持原样
ELSE --------用“抠字眼”的方法把周围的计数器拿出来修改再拼回去,比较难看的写法
CASE WHEN e.r>1 AND e.c>1 THEN SUBSTR(t.cnt_str,1,e.cell_id-:v_width-2)
||CASE WHEN e.cnt1>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n1,1)-1) ELSE SUBSTR(t.cnt_str,e.n1,1) END
||CASE WHEN e.cnt2>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n2,1)-1) ELSE SUBSTR(t.cnt_str,e.n2,1) END
||CASE WHEN e.cnt3>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n3,1)-1) WHEN e.cnt3=0 THEN SUBSTR(t.cnt_str,e.n3,1) END
||CASE WHEN e.cnt3 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n3+1,e.n4-e.n3-1) ELSE SUBSTR(t.cnt_str,e.n2+1,e.n4-e.n2-1) END
||CASE WHEN e.cnt4>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n4,1)-1) ELSE SUBSTR(t.cnt_str,e.n4,1) END
||SUBSTR(t.cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n5+1,e.n6-e.n5-1) ELSE SUBSTR(t.cnt_str,e.cell_id+1,e.n6-e.cell_id-1) END
||CASE WHEN e.cnt6>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n6,1)-1) ELSE SUBSTR(t.cnt_str,e.n6,1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n7,1)-1) ELSE SUBSTR(t.cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n8+1) ELSE SUBSTR(t.cnt_str,e.n7+1) END
END
WHEN e.r=1 AND e.c>1 THEN SUBSTR(t.cnt_str,1,e.cell_id-2)
||CASE WHEN e.cnt4>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n4,1)-1) ELSE SUBSTR(t.cnt_str,e.n4,1) END
||SUBSTR(t.cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n5+1,e.n6-e.n5-1) ELSE SUBSTR(t.cnt_str,e.cell_id+1,e.n6-e.cell_id-1) END
||CASE WHEN e.cnt6>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n6,1)-1) ELSE SUBSTR(t.cnt_str,e.n6,1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n7,1)-1) ELSE SUBSTR(t.cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n8+1) ELSE SUBSTR(t.cnt_str,e.n7+1) END
END
WHEN e.r>1 AND e.c=1 THEN SUBSTR(t.cnt_str,1,e.cell_id-:v_width-1)
||CASE WHEN e.cnt2>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n2,1)-1) ELSE SUBSTR(t.cnt_str,e.n2,1) END
||CASE WHEN e.cnt3>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n3,1)-1) WHEN e.cnt3=0 THEN SUBSTR(t.cnt_str,e.n3,1) END
||CASE WHEN e.cnt3 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n3+1,e.cell_id-e.n3-1) END
||SUBSTR(t.cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n5+1,e.n7-e.n5-1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n7,1)-1) ELSE SUBSTR(t.cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n8+1) ELSE SUBSTR(t.cnt_str,e.n7+1) END
END
WHEN e.r=1 AND e.c=1 THEN SUBSTR(t.cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n5+1,e.n7-e.n5-1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n7,1)-1) ELSE SUBSTR(t.cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.cnt_str,e.n8+1) ELSE SUBSTR(t.cnt_str,e.n7+1) END
END
END
END ----- 更新地雷计数器结束
,m_cnt + guess.mine ---- 总雷数递增或不变,取决于当前猜测
,t.empty_id+1 ------- 空单元格编号推进
,SUBSTR(res,1,cell_id-1)||CASE WHEN guess.mine=1 THEN '*' ELSE ' ' END||SUBSTR(res,cell_id+1) ---在输出结果中嵌入地雷
----------用“抠字眼”的方法把周围的计数器拿出来修改再拼回去,比较难看的写法
,CASE WHEN e.r>1 AND e.c>1 THEN SUBSTR(t.empty_cnt_str,1,e.cell_id-:v_width-2)
||CASE WHEN e.cnt1>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n1,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n1,1) END
||CASE WHEN e.cnt2>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n2,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n2,1) END
||CASE WHEN e.cnt3>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n3,1)-1) WHEN e.cnt3=0 THEN SUBSTR(t.empty_cnt_str,e.n3,1) END
||CASE WHEN e.cnt3 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n3+1,e.n4-e.n3-1) ELSE SUBSTR(t.empty_cnt_str,e.n2+1,e.n4-e.n2-1) END
||CASE WHEN e.cnt4>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n4,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n4,1) END
||SUBSTR(t.empty_cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.empty_cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.empty_cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n5+1,e.n6-e.n5-1) ELSE SUBSTR(t.empty_cnt_str,e.cell_id+1,e.n6-e.cell_id-1) END
||CASE WHEN e.cnt6>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n6,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n6,1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n7,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.empty_cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n8+1) ELSE SUBSTR(t.empty_cnt_str,e.n7+1) END
END
WHEN e.r=1 AND e.c>1 THEN SUBSTR(t.empty_cnt_str,1,e.cell_id-2)
||CASE WHEN e.cnt4>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n4,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n4,1) END
||SUBSTR(t.empty_cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.empty_cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.empty_cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n5+1,e.n6-e.n5-1) ELSE SUBSTR(t.empty_cnt_str,e.cell_id+1,e.n6-e.cell_id-1) END
||CASE WHEN e.cnt6>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n6,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n6,1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n7,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.empty_cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n8+1) ELSE SUBSTR(t.empty_cnt_str,e.n7+1) END
END
WHEN e.r>1 AND e.c=1 THEN SUBSTR(t.empty_cnt_str,1,e.cell_id-:v_width-1)
||CASE WHEN e.cnt2>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n2,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n2,1) END
||CASE WHEN e.cnt3>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n3,1)-1) WHEN e.cnt3=0 THEN SUBSTR(t.empty_cnt_str,e.n3,1) END
||CASE WHEN e.cnt3 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n3+1,e.cell_id-e.n3-1) END
||SUBSTR(t.empty_cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.empty_cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.empty_cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n5+1,e.n7-e.n5-1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n7,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.empty_cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n8+1) ELSE SUBSTR(t.empty_cnt_str,e.n7+1) END
END
WHEN e.r=1 AND e.c=1 THEN SUBSTR(t.empty_cnt_str,e.cell_id,1)
||CASE WHEN e.cnt5>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n5,1)-1) WHEN e.cnt5=0 THEN SUBSTR(t.empty_cnt_str,e.n5,1) END
||CASE WHEN e.r=:V_HEIGHT THEN SUBSTR(t.empty_cnt_str,e.n5+1)
ELSE CASE WHEN e.cnt5 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n5+1,e.n7-e.n5-1) END
||CASE WHEN e.cnt7>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n7,1)-1) ELSE SUBSTR(t.empty_cnt_str,e.n7,1) END
||CASE WHEN e.cnt8>0 THEN TO_CHAR(SUBSTR(t.empty_cnt_str,e.n8,1)-1) WHEN e.cnt8=0 THEN SUBSTR(t.empty_cnt_str,e.n8,1) END
||CASE WHEN e.cnt8 IS NOT NULL THEN SUBSTR(t.empty_cnt_str,e.n8+1) ELSE SUBSTR(t.empty_cnt_str,e.n7+1) END
END
end
FROM t
,empty_cells e
,guess ---和两种猜测做笛卡尔积:0:没有地雷,1:有地雷
WHERE t.empty_id<=(SELECT MAX(empty_id) FROM empty_cells) ------- 空位编号推进直至最大
AND t.empty_id=e.empty_id ------连接到empty_cells表,取当前猜测空位的坐标、邻居等信息
AND t.m_cnt<:v_cnt ---- 总雷数不能溢出
AND CASE WHEN SUBSTR(t.res,e.n1,1)=' ' OR SUBSTR(t.cnt_str,e.n1,1)='0' THEN 0 ---- 如果邻居已经被猜为无雷,或者计数器已经饱和,必须猜0
------- 如果邻居剩下未猜格子数等于未分配地雷数,必须猜1
WHEN SUBSTR(t.res,e.n1,1)='*' OR e.cnt1>0 AND SUBSTR(t.empty_cnt_str,e.n1,1)=SUBSTR(t.cnt_str,e.n1,1) THEN 1
ELSE guess.mine ---- 否则不限
END=guess.mine
AND CASE WHEN SUBSTR(t.res,e.n2,1)=' ' OR SUBSTR(t.cnt_str,e.n2,1)='0' THEN 0 -------邻居2-8的规则同邻居1
WHEN SUBSTR(t.res,e.n2,1)='*' OR e.cnt2>0 AND SUBSTR(t.empty_cnt_str,e.n2,1)=SUBSTR(t.cnt_str,e.n2,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.cnt_str,e.n3,1)='0' THEN 0
WHEN e.cnt3>0 AND SUBSTR(t.empty_cnt_str,e.n3,1)=SUBSTR(t.cnt_str,e.n3,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.res,e.n4,1)=' ' OR SUBSTR(t.cnt_str,e.n4,1)='0' THEN 0
WHEN SUBSTR(t.res,e.n4,1)='*' OR e.cnt4>0 AND SUBSTR(t.empty_cnt_str,e.n4,1)=SUBSTR(t.cnt_str,e.n4,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.cnt_str,e.n5,1)='0' THEN 0 ---- 从5号邻居开始为未猜测,去掉前两个规则的判断
WHEN e.cnt5>0 AND SUBSTR(t.empty_cnt_str,e.n5,1)=SUBSTR(t.cnt_str,e.n5,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.cnt_str,e.n6,1)='0' THEN 0
WHEN e.cnt6>0 AND SUBSTR(t.empty_cnt_str,e.n6,1)=SUBSTR(t.cnt_str,e.n6,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.cnt_str,e.n7,1)='0' THEN 0
WHEN e.cnt7>0 AND SUBSTR(t.empty_cnt_str,e.n7,1)=SUBSTR(t.cnt_str,e.n7,1) THEN 1
ELSE guess.mine
END=guess.mine
AND CASE WHEN SUBSTR(t.cnt_str,e.n8,1)='0' THEN 0
WHEN e.cnt8>0 AND SUBSTR(t.empty_cnt_str,e.n8,1)=SUBSTR(t.cnt_str,e.n8,1) THEN 1
ELSE guess.mine
END=guess.mine
)
SELECT res
FROM t
WHERE m_cnt=:v_cnt ---------- 总雷数必须符合
AND cnt_str=TRANSLATE(:v_str,'12345678','00000000') ------ 正确的猜测结果,应该所有的计数器都为零
AND ROWNUM=1 ---------- 有多种答案的话只选任一种
;
|
|