这是 Anton Scheffer 写的：
with x( s, ind ) as
( select sud, instr( sud, ' ' )
from ( select '5376195986 8 6 348 317 2 6 6284195879' sud from dual )
union all
select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
, instr( s, ' ', ind + 1 )
from x
, ( select to_char( rownum ) z
from dual
connect by rownum0
and not exists ( select null
from ( select rownum lp
from dual
connect by rownum <= 9
)
where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
orz = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
orz = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
+ trunc( ( ind - 1 ) / 27 ) * 27 + lp
+ trunc( ( lp - 1 ) / 3 ) * 6
, 1 )
)
)
select s
from x
where ind = 0
/
我自己写的版本，代码多一些但是效率较高：
VAR str VARCHAR2(81);
EXEC :str := '5376195986 8 6 348 317 2 6 6284195879';
WITH grid AS (
SELECT LEVEL
AS pos ------- 字符串位置1-18编号
,FLOOR((LEVEL-1)/9)
AS r ------- 哪一行, 编号0-8
,MOD(LEVEL-1,9)
AS c ------- 哪一列, 编号0-8
,FLOOR(FLOOR((LEVEL-1)/9)/3)*3 + FLOOR(MOD(LEVEL-1,9)/3) AS g ------- 哪一格, 编号0-8
FROM DUAL
CONNECT BY LEVEL<=81
)
,all_pos AS ( ------ 每个POS上的字符，如果是n=1-9中的任何一个，将会到哪个位置取占用标记, 这是所有位置(1-81)和所有n的笛卡尔积。递归时求出哪个n可以用在哪个位置
SELECT pos,n
,grid.r*9+n AS r --------- 所有行占用标记，按照n=1-9凑在一起：第0行的9个||第1行的9个||...||第8行的9个, 这里根据n和r算出在占用标记里的位置
,grid.c*9+n AS c
,grid.g*9+n AS g
FROM grid,(SELECT LEVEL n FROM DUAL CONNECT BY LEVEL<=9)
)
,g2 AS
--------- 所有行占用标记，按照n=1-9凑在一起：第0行的9个||第1行的9个||...||第8行的9个
(SELECT LPAD(SUM(n) OVER(PARTITION BY r),9,'0') rs
,LPAD(SUM(n) OVER(PARTITION BY c),9,'0') cs
,LPAD(SUM(n) OVER(PARTITION BY g),9,'0') gs
,r,c,g
FROM (SELECT CASE WHEN SUBSTR(:str,pos,1) BETWEEN '1' AND '9' THEN POWER(10,9-SUBSTR(:str,pos,1)) ELSE 0 END n
,r,c,g
FROM grid
)
)
,t(s,rs,cs,gs,next_pos,lvl) AS (
SELECT CAST(:str AS VARCHAR2(81)) ----- 拼出初始的占用标记串
,CAST((SELECT LISTAGG(rs) WITHIN GROUP(ORDER BY r) FROM (SELECT DISTINCT r,rs FROM g2)) AS VARCHAR2(81)) rs
,CAST((SELECT LISTAGG(cs) WITHIN GROUP(ORDER BY c) FROM (SELECT DISTINCT c,cs FROM g2)) AS VARCHAR2(81)) cs
,CAST((SELECT LISTAGG(gs) WITHIN GROUP(ORDER BY g) FROM (SELECT DISTINCT g,gs FROM g2)) AS VARCHAR2(81)) gs
,INSTR(:str,' ')
,1
FROM DUAL
UNION ALL
SELECT SUBSTR(t.s,1,t.next_pos-1)||a.n||SUBSTR(t.s,t.next_pos+1)
,SUBSTR(t.rs,1,a.r-1)||'1'||SUBSTR(t.rs,a.r+1)
,SUBSTR(t.cs,1,a.c-1)||'1'||SUBSTR(t.cs,a.c+1)
,SUBSTR(t.gs,1,a.g-1)||'1'||SUBSTR(t.gs,a.g+1)
,INSTR(t.s,' ',t.next_pos+1)
,lvl+1
FROM t
,all_pos a
WHERE t.next_pos = a.pos
AND SUBSTR(t.rs,a.r,1)='0'
AND SUBSTR(t.cs,a.c,1)='0'
AND SUBSTR(t.gs,a.g,1)='0'
)
SELECT t.s FROM t WHERE next_pos=0;
|
