| 订阅 | 在线投稿
分享
 
 
 

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

来源:互联网  宽屏版  评论
2008-06-01 02:20:50

算法问题:用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 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度旋转, 在顺序固定的情况下, 应该就是行列转换的问题了。

不过如果要做成圆形的, 我觉得不太可能了, 正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> 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

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

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

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

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

 
算法问题:用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 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度旋转, 在顺序固定的情况下, 应该就是行列转换的问题了。 不过如果要做成圆形的, 我觉得不太可能了, 正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> 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 ----------------------------------------------------------- 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 ----------------------------------------------------- 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
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有