|
算法问题 用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] 下一页 |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 