科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航



ZDNet>网络频道>ZD评测>如何用SQL写出当M*N时的螺旋矩阵算法

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

本文将为大家介绍如何用SQL写出当M*N时的螺旋矩阵算法。

来源:天新网 2008年03月22日

关键字:SQL SQL Server Mssql 数据库

     19

   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

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

    10

   11 19 9

   12 20 24 18 8

   1 13 21 25 23 17 7

   2 14 22 16 6

   3 15 5

   4

  已选择7行。

  SQL> exec :n := 9;

  PL/SQL 过程已成功完成。

  SQL> /

  STR

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

   13

   14 26 12

   15 27 35 25 11

   16 28 36 40 34 24 10

   1 17 29 37 41 39 33 23 9

   2 18 30 38 32 22 8

   3 19 31 21 7

   4 20 6

   5

  已选择9行。

  SQL> exec :n := 8

  PL/SQL 过程已成功完成。

  SQL> /

  STR

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

   5 4

   6 18 17 3

   7 19 27 26 16 2

   8 20 28 32 31 25 15 1

   9 21 29 30 24 14

   10 22 23 13

   11 12

  

  对于比较大的N值, 需对"顺时针螺旋特征码"的组成进行适当修改:

  

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

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

   2 from (select i, j,

   3 case when rank() over(order by tag) - floor(:n * :n / 2) <= 0 then " "

   4 else to_char(rank() over(order by tag) - floor(:n * :n / 2), "9999") end as rank,

   5 min(j) over(partition by i) minj

   6 from (select i,

   7 j,

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

   9 case greatest(i - j, i + j - :n - 1, j - i, :n - i - j + 1)

   10 when i - j then

   11 chr(:n - (i - j)) || "1" || chr(i)

   12 when i + j - :n - 1 then

   13 chr(:n - (i + j - :n - 1)) || "2" || chr(j)

   14 when j - i then

   15 chr(:n - (j - i)) || "3" || chr((:n - i))

   16 when :n - i - j + 1 then

   17 chr(:n - (:n - i - j + 1)) || "4" || chr(i)

   18 end as tag

   19 from (select level as i from dual connect by level <= :n) a,

   20 (select level as j from dual connect by level <= :n) b

   21 -- where abs(i - j) < floor(:n / 2 + .6)

   22 -- and i + j between floor(:n / 2 + .6) + 1 and floor(:n / 2 + .6) + :n

   23 )

   24 )

   25 start with j = minj

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

   27 group by i

   28* order by i

  SQL> /

  STR

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

     1 12 11 10

   2 13 16 9

   3 14 15 8

   4 5 6 7

  SQL> exec :n := 5;

  PL/SQL 过程已成功完成。

  SQL> /

  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>

  我们可以尝试填足一下:

  

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

  SQL> exec :n := 5

  PL/SQL 过程已成功完成。

  SQL> select replace(max(sys_connect_by_path(rank, ",")), ",") str

   2 from (select i, j,

   3 case when rank() over(order by tag) - floor(:n * :n / 2) <= 0 then " "

   4 else to_char(rank() over(order by tag) - floor(:n * :n / 2), "9999") end as rank,

   5 min(j) over(partition by i) minj

   6 from (select i,

   7 j,

   8 -- 顺时针螺旋特征码 counter-clockwise

   9 case greatest(i - j, i + j - :n - 1, j - i, :n - i - j + 1)

   10 when i - j then

   11 :n - (i - j) || "1" || i

   12 when i + j - :n - 1 then

   13 :n - (i + j - :n - 1) || "2" || j

   14 when j - i then

   15 :n - (j - i) || "3" || (:n - i)

   16 when :n - i - j + 1 then

   17 :n - (:n - i - j + 1) || "4" || i

   18 end as tag

   19 from (select level as i from dual connect by level <= :n) a,

   20 (select level as j from dual connect by level <= :n) b

   21 -- where abs(i - j) < floor(:n / 2 + .6)

   22 -- and i + j between floor(:n / 2 + .6) + 1 and floor(:n / 2 + .6) + :n

   23 )

   24 )

   25 start with j = minj

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

   27 group by i

   28 order by i;

  STR

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

   7

   8 12 6

   1 9 13 11 5

   2 10 4

   3

  SQL> exec :n := 7;

  PL/SQL 过程已成功完成。

  SQL> /

  STR

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

    不过如果要做成圆形的, 我觉得不太可能了, 正n边形倒是可以考虑, 不过要看n的值是多大, 如果趋于正无穷, 那就是圆了, 下面我们来具体说一下这个螺旋特征码的算法原理。

  螺旋总要有个起点, 就用上面的那个结果来说明吧,起点是(1,1), 如果是顺时针的话, 旋转时依次走过的途径是上->右->下->左->上->右->下->左..., 知道最后在螺旋中心结束, 但是可以注意到旋转是会越来越远离外边界

  根据这个我们就可以获取螺旋特征码了

  4*4的矩阵, 那么可以认为 i=1, j=1, i=4, j=4, 这就是这个螺旋的4个边界, 顺时针旋转时, 离边界越近, 那么顺序就越靠前, 当距离边界相同时, 边界的优先级就要根据 上右下左(起点为1,1, 顺时针旋转的边界优先级) 而定了, 如果这个也相同, 那么就要根据这个点离前一个边界的距离而定, 离的越近, 优先级越高, 根据以上规则, 可以得出特征码共有三位, 第一位代表距离边界的距离, 第二位代表距离哪个边界最近(我的sql中用1,2,3,4分别表示四个边界), 第三位代表距离前一个边界的距离(因为目的是为了排序, 计算时没有严格按照这个距离值进行表示^_^)

  对应上面螺旋特征码的规则, 使用case least(...)判断离边界的距离和距离最近的边界是那个边界, when ... then后的取值再确定距离前一个边界的距离, 这样就完成了特征码, 剩下的就是对特征码排序和行列转换了。

  现在现换一下思路, 用SYS_CONNECT_BY_PATH函数, 实现N的矩阵生成:

  

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

  SQL> var n number;

  SQL> exec :n := 3;

  PL/SQL 过程已成功完成。

  SQL> 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, :n - i, :n - j, i - 1)

   8 when j - 1 then

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

   10 when :n - i then

   11 (:n - i) || "2" || j

   12 when :n - j then

   13 (:n - j) || "3" || (:n - i)

   14 when i - 1 then

   15 (i - 1) || "4" || (:n - j)

   16 end as tag

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

   18 (select level as j from dual connect by level <= :n) 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;

  STR

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

   1 8 7

   2 9 6

   3 4 5

  SQL> exec :n := 4;

  PL/SQL 过程已成功完成。

  SQL> /

  STR

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

     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 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 2 3 4

   12 13 14 5

   11 16 15 6

   10 9 8 7

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

  以上两种旋转都是由外向内的, 如果有兴趣也可以做成由内向外的。

  不过如果大家还要把结果90度旋转, 在顺序固定的情况下, 应该就是行列转换的问题了。

    算法问题:用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

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

推广二维码
邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题