如何用SQL写出当M*N时的螺旋矩阵算法

　　 20 40 18

　　 21 41 57 39 17

　　 22 42 58 70 56 38 16

　　 23 43 59 71 79 69 55 37 15

　　 24 44 60 72 80 84 78 68 54 36 14

　　 1 25 45 61 73 81 85 83 77 67 53 35 13

　　 2 26 46 62 74 82 76 66 52 34 12

　　 3 27 47 63 75 65 51 33 11

　　 4 28 48 64 50 32 10

　　 5 29 49 31 9

　　 6 30 8

　　 7

　　-------------------------------------------------------

　　想来是的, 这样你看如何?

　　代码:--------------------------------------------------

　　1 select replace(max(sys_connect_by_path(rank, ',')), ',') str

　　 2 from (select i, j,

　　 3 to_char(rank() over(order by tag), '9999') as rank

　　 4 from (select i,

　　 5 j,

　　 6 -- 逆时针螺旋特征码 counter-clockwise

　　 7 case least(j - 1, &&1 - i, &1 - j, i - 1)

　　 8 when j - 1 then

　　 9 (j - 1) || '1' || i

　　 10 when &1 - i then

　　 11 (&1 - i) || '2' || j

　　 12 when &1 - j then

　　 13 (&1 - j) || '3' || (&1 - i)

　　 14 when i - 1 then

　　 15 (i - 1) || '4' || (&1 - j)

　　 16 end as tag

　　 17 from (select level as i from dual connect by level <= &1) a,

　　 18 (select level as j from dual connect by level <= &1) b

　　 19 )

　　 20 )

　　 21 start with j = 1

　　 22 connect by j - 1 = prior j and i = prior i

　　 23 group by i

　　 24* order by i

　　SQL> /

　　输入 1 的值: 5

　　原值 7: case least(j - 1, &&1 - i, &1 - j, i - 1)

　　新值 7: case least(j - 1, 5 - i, 5 - j, i - 1)

　　原值 10: when &1 - i then

　　新值 10: when 5 - i then

　　原值 11: (&1 - i) || '2' || j

　　新值 11: (5 - i) || '2' || j

　　原值 12: when &1 - j then

　　新值 12: when 5 - j then

　　原值 13: (&1 - j) || '3' || (&1 - i)

　　新值 13: (5 - j) || '3' || (5 - i)

　　原值 15: (i - 1) || '4' || (&1 - j)

　　新值 15: (i - 1) || '4' || (5 - j)

　　原值 17: from (select level as i from dual connect by level <= &1) a,

　　新值 17: from (select level as i from dual connect by level <= 5) a,

　　原值 18: (select level as j from dual connect by level <= &1) b

　　新值 18: (select level as j from dual connect by level <= 5) b

　　STR

　　-----------------------------------------------------

　　 1 16 15 14 13

　　 2 17 24 23 12

　　 3 18 25 22 11

　　 4 19 20 21 10

　　 5 6 7 8 9

　　SQL>--------------------------------------------------

　　使用前, 给声明m和n并赋值

　　代码:-------------------------------------------------

　　var n number;

　　var m number;

　　exec :n := &n; :m=&m;

　　with t as (

　　 select :n as n, :m as m from dual

　　)

　　select replace(max(sys_connect_by_path(rank, ',')), ',') str

　　 from (select i, j, to_char(rank() over(order by tag), '999999') as rank

　　 from (select i,

　　 j,

　　 -- 顺时针螺旋特征码 clockwise

　　 case least(i - 1, m - j, n - i, j - 1)

　　 when i - 1 then

　　 to_char(i - 1, 'fm0000') || '1' ||

　　 to_char(j - 1, 'fm0000')

　　 when m - j then

　　 to_char(m - j, 'fm0000') || '2' ||

　　 to_char(i - 1, 'fm0000')

　　 when n - i then

　　 to_char(n - i, 'fm0000') || '3' ||

　　 to_char(m - j, 'fm0000')

　　 when j - 1 then

　　 to_char(j - 1, 'fm0000') || '4' ||

　　 to_char(n - i, 'fm0000')

　　 end as tag

　　 from (select n, level as i from t connect by level <= n) a,

　　 (select m, level as j from t connect by level <= m) b))

　　 start with j = 1

　　connect by j - 1 = prior j and i = prior i

　　 group by i

　　--------------------------------------------------------------