算法问题 用SQL写出当M*N时的螺旋矩阵算法
如下是一个4*4的矩阵:

1 12 11 10
2 13 16  9
3 14 15  8
4  5  6  7

按照上面矩阵的规律, 请用SQL写出当M*N时的矩阵算法

实现的sql和效果:

代码:--------------------------------------------------------------------------------
SQL> -- 逆时针的
SQL> select --i,
  2         sum(decode(j, 1, rn)) as co11,
  3         sum(decode(j, 2, rn)) as co12,
  4         sum(decode(j, 3, rn)) as co13,
  5         sum(decode(j, 4, rn)) as co14
  6    from (select i, j, rank() over(order by tag) as rn
  7            from (select i,
  8                         j,
  9                         -- 逆时针螺旋特征码 counter-clockwise
 10                         case least(j - 1, 4 - i, 4 - j, i - 1)
 11                           when j - 1 then
 12                            (j - 1) || ''''1'''' || i
 13                           when 4-i then
 14                            (4 - i) || ''''2'''' || j
 15                           when 4 - j then
 16                            (4 - j) || ''''3'''' || (4 - i)
 17                           when i - 1 then
 18                            (i - 1) || ''''4'''' || (4 - j)
 19                         end as tag
 20                    from (select level as i from dual connect by level <= 4) a,
 21                         (select level as j from dual connect by level <= 4) b))
 22   group by i
 23  /

      CO11       CO12       CO13       CO14
---------- ---------- ---------- ----------
         1         12         11         10
         2         13         16          9
         3         14         15          8
         4          5          6          7

SQL> -- 顺时针的
SQL> select --i,
  2         sum(decode(j, 1, rn)) as co11,
  3         sum(decode(j, 2, rn)) as co12,
  4         sum(decode(j, 3, rn)) as co13,
  5         sum(decode(j, 4, rn)) as co14
  6    from (select i, j, rank() over(order by tag) as rn
  7            from (select i,
  8                         j,
  9                         -- 顺时针螺旋特征码 clockwise
 10                         case least(i - 1, 4 - j, 4 - i, j - 1)
 11                           when i - 1 then
 12                            (i - 1) || ''''1'''' || j
 13                           when 4 - j then
 14                            (4 - j) || ''''2'''' || i
 15                           when 4 - i then
 16                            (4 - i) || ''''3'''' || (4 - j)
 17                           when j - 1 then
 18                            (j - 1) || ''''4'''' || (4 - i)
 19                         end as tag
 20                  

[1] [2] [3] [4] [5] [6]  下一页