有2个table,
t1 的结构是
create table t1 (id number(3), nid number(3), value(3));
insert into t1 values (1,1,4);insert into t1 values (1,2,5);insert into t1 values (1,3,6);t2的结构是
create table t2 (id number(3), value1(3),value2(3),value3(3));希望插入到t2的数据是(1,4,5,6);请问怎么用一条sql实现
t1 的结构是
create table t1 (id number(3), nid number(3), value(3));
insert into t1 values (1,1,4);insert into t1 values (1,2,5);insert into t1 values (1,3,6);t2的结构是
create table t2 (id number(3), value1(3),value2(3),value3(3));希望插入到t2的数据是(1,4,5,6);请问怎么用一条sql实现
id , nid ,value
1 1 4
1 2 5
1 3 6
然后根据t1的数据 希望 插入到t2 的数据是
id value1 value2 value3
1 4 5 6
type
v_varray is varray(3) of numeric(1);
v_var v_varray;
cursor a is
select id,value from t1;
i int;
begin
i=0;
for a1 in a loop
v_var[i]:=a1.value;
i=i+1;
end loop;
insert into t2 values(1,v_var[0],v_var[1],v_var[2])
commit;
exception
rollback;
end;
insert into t2 values(select id,case t1.nid when 1 then t1.value else null end v1,
case t1.nid when 2 then t1.value else null end v2,
case t1.nid when 3 then t1.value else null end v3
from t1 where id = 1 group by id)
这是一次产生一条的语句。
一次产生多条的。
select id,case t1.nid when 1 then t1.value else null end v1,
case t1.nid when 2 then t1.value else null end v2,
case t1.nid when 3 then t1.value else null end v3
into t2
from t1 group by id
select 1,
value1,
(select value from t1 where t1.id=1 and t1.nid = 2),
(select value from t1 where t1.id=1 and t1.nid = 3)
from t1
只能这样了,还没办法判断是不是已经到底了,就是nid=max(nid)只能写死了。
最好是用游标来,快捷简单。
insert into t2
select id,case t1.nid when 1 then t1.value else null end v1,
case t1.nid when 2 then t1.value else null end v2,
case t1.nid when 3 then t1.value else null end v3
from t1 where id = 1 group by id
这是一次产生一条的语句。
一次产生多条的。
insert into t2
select id,case t1.nid when 1 then t1.value else null end v1,
case t1.nid when 2 then t1.value else null end v2,
case t1.nid when 3 then t1.value else null end v3
from t1 group by id
开始那条多了一个values,想改又改不了,就重发了。