楼主指的是用oracle的sql实现还是可以用函数实现?A B C D是4个数组还是一个表的4个列?
declare type type_array is table of number index by binary_integer; type type_arrays is table of type_array; tp_n type_array; tp_ns type_arrays; i_l_count pls_integer; i_l_count_2 pls_integer; begin for i in 1 .. 10 loop tp_n(i) := 11 - i; end loop;
i_l_count := 0; i_l_count_2 := 1; for j in 1 .. tp_n.count loop i_l_count := i_l_count + 1; if i_l_count = 5 then i_l_count := 1; i_l_count_2 := i_l_count_2 + 1; end if;
if mod(i_l_count_2, 2) = 0 then tp_ns(5 - i_l_count)(tp_ns(5 - i_l_count).count + 1) := tp_n(j); else tp_ns(i_l_count)(tp_ns(i_l_count).count + 1) := tp_n(j); end if; end loop;
for k in 1 .. tp_ns.count loop dbms_output.put('数组' || k || '的值:' );
for l in 1 .. tp_ns(k).count loop Dbms_Output.put(tp_ns(k)(l) || ','); end loop; dbms_output.put_line(null); end loop; end;
假设楼主是要用SQL实现,A B C D为表的4个列,那么对应的SQL语句如下:with F as ( select 10 as c1 from dual union select 1 from dual union select 4 from dual union select 6 from dual union select 3 from dual union select 5 from dual union select 8 from dual union select 11 from dual ) select sum(AA) as A,sum(BB) as B,sum(CC) as C,sum(DD) as D from ( select decode(col_no, 1, c1, 0) as AA, decode(col_no, 2, c1, 0) as BB, decode(col_no, 3, c1, 0) as CC, decode(col_no, 0, c1, 0) as DD, row_no from (select c1,mod(rownum,4) as col_no,floor((rownum + 3)/4) as row_no,rownum from f order by c1) ) group by row_no;
type type_array is table of number index by binary_integer;
type type_arrays is table of type_array;
tp_n type_array;
tp_ns type_arrays;
i_l_count pls_integer;
i_l_count_2 pls_integer;
begin
for i in 1 .. 10 loop
tp_n(i) := 11 - i;
end loop;
tp_ns := type_arrays();
tp_ns.extend;
tp_ns.extend;
tp_ns.extend;
tp_ns.extend;
i_l_count := 0;
i_l_count_2 := 1;
for j in 1 .. tp_n.count loop
i_l_count := i_l_count + 1;
if i_l_count = 5 then
i_l_count := 1;
i_l_count_2 := i_l_count_2 + 1;
end if;
if mod(i_l_count_2, 2) = 0 then
tp_ns(5 - i_l_count)(tp_ns(5 - i_l_count).count + 1) := tp_n(j);
else
tp_ns(i_l_count)(tp_ns(i_l_count).count + 1) := tp_n(j);
end if;
end loop;
for k in 1 .. tp_ns.count loop
dbms_output.put('数组' || k || '的值:' );
for l in 1 .. tp_ns(k).count loop
Dbms_Output.put(tp_ns(k)(l) || ',');
end loop;
dbms_output.put_line(null);
end loop;
end;
(
select 10 as c1 from dual
union
select 1 from dual
union
select 4 from dual
union
select 6 from dual
union
select 3 from dual
union
select 5 from dual
union
select 8 from dual
union
select 11 from dual
)
select sum(AA) as A,sum(BB) as B,sum(CC) as C,sum(DD) as D from
(
select decode(col_no, 1, c1, 0) as AA,
decode(col_no, 2, c1, 0) as BB,
decode(col_no, 3, c1, 0) as CC,
decode(col_no, 0, c1, 0) as DD,
row_no
from
(select c1,mod(rownum,4) as col_no,floor((rownum + 3)/4) as row_no,rownum from f order by c1)
)
group by row_no;