[原]SQL解决“俯瞰金字塔”矩阵

原题是邀月的大作《一个类似于杨辉三角的数组算法思路》，我觉得原文中所谓的“类似于杨辉三角”这个算法比较模糊，所以改成“俯瞰金字塔”，这样显得更形象一点。

 1 1 1 1 1 1 1 1 1 1 1
 1 2 2 2 2 2 2 2 2 2 1
 1 2 3 3 3 3 3 3 3 2 1
 1 2 3 4 4 4 4 4 3 2 1
 1 2 3 4 5 5 5 4 3 2 1
 1 2 3 4 5 6 5 4 3 2 1
 1 2 3 4 5 5 5 4 3 2 1
 1 2 3 4 4 4 4 4 3 2 1
 1 2 3 3 3 3 3 3 3 2 1
 1 2 2 2 2 2 2 2 2 2 1
 1 1 1 1 1 1 1 1 1 1 1

如果将数字看成是高度的话，中间高，四周低是一个很形象的金字塔哈。

废话少说，上SQL：

 

Oracle，SQL Plus中执行：

var v_level number;
exec :v_level := 10;  /* <- n在这里修改 */
with seq as (
  select level v from  dual connect  by level<= :v_level+1
),
matrix as (
  select r.v r,c.v c from seq r,seq c 
),
m01 as (
select 
  r,c,
  least(abs(case when r<=ceil(:v_level/2) then r else :v_level+1+1-r end),
        abs(case when c<=ceil(:v_level/2) then c else :v_level+1+1-c end)) v
from matrix order by r,c
)
select SYS_CONNECT_BY_PATH( v, ' ') matrix
from m01
where level=:v_level+1
start with c=1
connect by prior r=r 
       and prior c=c-1
order by r ;

MATRIX
-------------------------
 1 1 1 1 1 1 1 1 1 1 1
 1 2 2 2 2 2 2 2 2 2 1
 1 2 3 3 3 3 3 3 3 2 1
 1 2 3 4 4 4 4 4 3 2 1
 1 2 3 4 5 5 5 4 3 2 1
 1 2 3 4 5 6 5 4 3 2 1
 1 2 3 4 5 5 5 4 3 2 1
 1 2 3 4 4 4 4 4 3 2 1
 1 2 3 3 3 3 3 3 3 2 1
 1 2 2 2 2 2 2 2 2 2 1
 1 1 1 1 1 1 1 1 1 1 1

解析一下，其实思路很简单，很暴力，设定 n=10 吧，

select level v from dual connect by level<= 10+1

产生一个 1~11的序列；

select r.v r,c.v c from seq r,seq c

全连接，产生一个 11*11的矩阵(r,c)，r和c的取值范围在 1~11 之间

select
  r,c,
  least(abs(case when r<=ceil(10/2) then r else 10+1+1-r end),
        abs(case when c<=ceil(10/2) then c else 10+1+1-c end)) v
from matrix order by r,c

有点算法的味道吧，对于某一点(r,c)的数值 v 有以下等式：

v = least(abs(case when r<=ceil(10/2) then r else 10+1+1-r end),
          abs(case when c<=ceil(10/2) then c else 10+1+1-c end))

least 是求最小值的函数。

 

SQL Server（需要SQL Server 2005或以上）：

declare @level int;
set @level=10;  -- n在这里修改
with seq as (
  select v from ( 
	  select
		row_number() over (order by object_id) v
	  from sys.objects
  )a
  where v<=@level+1
),
matrix as (
select 
  r.v r,c.v c, ( select MIN(v) from 
         ( select case when r.v<=ceiling(@level/2) then r.v else @level+1+1-r.v end as v 
           union all 
           select case when c.v<=ceiling(@level/2) then c.v else @level+1+1-c.v end as v 
          )a
        ) as v
from seq r,seq c
),
cte as (
  select 0 as lvl,r,c,cast(v as varchar(100)) as line 
    from matrix where c=1
  union all 
  select lvl+1,m.r ,m.c , cast(c.line+' '+cast(m.v as varchar) as varchar(100)) as line 
    from cte c,matrix m 
    where c.r=m.r and m.c=c.c+1
)
select 
  line as matrix from cte
where lvl=@level
order by r;

MATRIX
-------------------------
1 1 1 1 1 1 1 1 1 1 1
1 2 2 2 2 2 2 2 2 2 1
1 2 3 3 3 3 3 3 3 2 1
1 2 3 4 4 4 4 4 3 2 1
1 2 3 4 5 5 5 4 3 2 1
1 2 3 4 5 6 5 4 3 2 1
1 2 3 4 5 5 5 4 3 2 1
1 2 3 4 4 4 4 4 3 2 1
1 2 3 3 3 3 3 3 3 2 1
1 2 2 2 2 2 2 2 2 2 1
1 1 1 1 1 1 1 1 1 1 1