[原]使用SQL将数组转换为矩阵

一个矩阵怎么保存呢？

    | 1 2 3 (ROW)
 ---+-------
  1 | 1 2 3
  2 | 4 5 6
  3 | 7 8 9
(COL)

通常的做法是保存成一个矩阵数组如下：

         R          C          V
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          4
         2          2          5
         2          3          6
         3          1          7
         3          2          8
         3          3          9

但是怎么转换回去呢？这个也很简单，估计大学C语言教材里就能找到例子，这里也不多说了，那么如何用SQL来解决这个问题呢？Thinking In Sets：

首先是Oracle的例子：

with array as (
	select 1 as r, 1 as c,1 as v from dual  union all
	select 1,2,2 from dual  union all
	select 1,3,3 from dual  union all
	select 2,1,4 from dual  union all
	select 2,2,5 from dual  union all
	select 2,3,6 from dual  union all
	select 3,1,7 from dual  union all
	select 3,2,8 from dual  union all
	select 3,3,9 from dual
)
select SYS_CONNECT_BY_PATH( v, ' ') matrix
from array
where level=(select count(distinct(c)) from array)
start with c=1
connect by prior r=r 
       and prior c=c-1
order by r ;

MATRIX
--------
 1 2 3
 4 5 6
 7 8 9

我们使用了Oracle的 connect by 进行递归层次查询获得结果：

再来看看SQL Server的例子（需要SQL Server 2005或以上版本）：

with array as (
	select 1 as r, 1 as c,1 as v  union all
	select 1,2,2  union all
	select 1,3,3  union all
	select 2,1,4  union all
	select 2,2,5  union all
	select 2,3,6  union all
	select 3,1,7  union all
	select 3,2,8  union all
	select 3,3,9 
),
cte as ( 
  select 1 as lvl,r,c,cast(v as varchar(50)) as line 
    from array where c=1
  union all
  select lvl+1, a.r, a.c, cast(c.line+' '+cast(a.v as varchar(10)) as varchar(50)) 
    from cte c, array a
    where c.r=a.r and a.c=c.c+1
)
select line as matrix from cte
where lvl=(select COUNT(distinct(c)) from array)
order by r;

matrix
--------
1 2 3
4 5 6
7 8 9

SQL Server 借助CTE语法实现了递归层次查询。

 

算法很简单：

第一步，找出c=1的所有数据，也就是第一列上的数据

第二步，在当前数据的同一行上（Oracle：prior r=r / SQL Server：c.r=a.r）寻找下一个数据（Oracle：prior c=c-1 / SQL Server：a.c=c.c+1）

递归调用第二步，直到找不到下一个数据为止。