走迷宮從2011算出2012的SQL程式碼

lt發表於2012-11-10

執行在Oracle 11.2,按照回退半條路也不行的規則

   ---------    ---------
   |   1   |    |   3   |
   |       |    |       |
---         -----        ---
  0           5          6
---         -----        ---
   |       |    |       |
   |   2   |    |   4   |
   ---------    ---------


with a as(
select level-1 l from dual connect by level<=7)
,t(n,lv,last,L2,path)as(
select 2011 n,1 lv, -1 last,-1 L2,cast('2011' as varchar(2000))path from dual 
union all
select case a.l 
             when 1 then n+7
             when 2 then n/2
             when 3 then n*3
             when 4 then n-5
        else n
        end,
        lv+case when a.l in (1,2,3,4) then 1 else 0 end,
        a.l,
        last,
        case a.l 
             when 1 then path||'+7'
             when 2 then path||'/2'
             when 3 then path||'*3'
             when 4 then path||'-5'
        else
            path
        end
from t,a 
where (last,l) in((-1,0),
          (0,1),
          (1,0),
          (0,2),
          (2,0),
          (1,5),
          (5,1),
          (2,5),
          (5,2),
          (3,5),
          (5,3),
          (4,5),
          (5,4),
          (4,6),
          (6,4),
          (3,6),
          (6,3))
    and l2<>l      
        AND (a.l<>2 OR a.l=2 AND MOD(n,2)=0)
        and lv<30
)
select path from t where n=2012 and last =6 and rownum=1;

PATH
-----------------------------------------------------------
2011+7/2+7/2+7-5*3/2+7/2+7*3-5/2+7/2+7-5*3-5*3/2+7-5*3/2+7-5

相關文章