用PLSQL解数独(SUDOKU)

boyheng · 发表于 2010-4-23 15:47

发觉这人比人。还真的是比死人。。

〇〇 · 发表于 2010-9-26 08:58

忘记newkid发过没有，贴在这里备忘
Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring. My collegue Lucas sees it as a substitute for Connect By based hierarchical querying, Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring. When I first was thinking about a pratical use for this feature I couldn’t come up with anything, but on second thought:: solving Sudokus!

Say you have a sudoku like:

To solve this sudoku you first have to transforms this to a single string by appending all rows together:

"53  7 6  195 98 6 8 6 34  8 3  17 2 6 6 28 419  5 8  79"
Past this string into a Recursive Subquery, run it and you get a new string with your solved sudoku:

view sourceprint?

Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring. My collegue Lucas sees it as a substitute for Connect By based hierarchical querying, Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring. When I first was thinking about a pratical use for this feature I couldn’t come up with anything, but on second thought:: solving Sudokus!

Say you have a sudoku like:

To solve this sudoku you first have to transforms this to a single string by appending all rows together:

"53  7 6  195 98 6 8 6 34  8 3  17 2 6 6 28 419  5 8  79"
Past this string into a Recursive Subquery, run it and you get a new string with your solved sudoku:

view sourceprint?

with x( s, ind ) as
( select sud, instr( sud, ' ' )
  from ( select '53  7 6  195 98 6 8 6 34  8 3  17 2 6 6 28 419  5 8  79' 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 rownum <= 9
   ) z
  where ind > 0
  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 )
               or z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
               or z = 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
/