以下是一个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
