有一表TA 字段为cid,data001,data002,data003,data004,sendtime
数据行例如: c001 1 2 3 4 2009-5-5 12:11:11
c002 10 20 30 40 2009-5-5 12:11:11
c003 11 22 33 44 2009-5-5 12:11:11 如何根据以上TA表,创建成拥有以下要求的视图V_TA:
视图的字段为 cid, datatype, datas, sendtime
c001 data001 1 2009-5-5 12:11:11
c001 data002 2 2009-5-5 12:11:11
c001 data003 3 2009-5-5 12:11:11
c001 data004 4 2009-5-5 12:11:11
c002 data001 10 2009-5-5 12:11:11
c002 data002 20 2009-5-5 12:11:11
c002 data003 30 2009-5-5 12:11:11
c002 data004 40 2009-5-5 12:11:11
c003 data001 11 2009-5-5 12:11:11
c003 data002 22 2009-5-5 12:11:11
c003 data003 33 2009-5-5 12:11:11
c003 data004 44 2009-5-5 12:11:11急,感谢了
数据行例如: c001 1 2 3 4 2009-5-5 12:11:11
c002 10 20 30 40 2009-5-5 12:11:11
c003 11 22 33 44 2009-5-5 12:11:11 如何根据以上TA表,创建成拥有以下要求的视图V_TA:
视图的字段为 cid, datatype, datas, sendtime
c001 data001 1 2009-5-5 12:11:11
c001 data002 2 2009-5-5 12:11:11
c001 data003 3 2009-5-5 12:11:11
c001 data004 4 2009-5-5 12:11:11
c002 data001 10 2009-5-5 12:11:11
c002 data002 20 2009-5-5 12:11:11
c002 data003 30 2009-5-5 12:11:11
c002 data004 40 2009-5-5 12:11:11
c003 data001 11 2009-5-5 12:11:11
c003 data002 22 2009-5-5 12:11:11
c003 data003 33 2009-5-5 12:11:11
c003 data004 44 2009-5-5 12:11:11急,感谢了
union all
select cid,'data002',data002,sendtime from ta
union all
select cid,'data003',data003,sendtime from ta
union all
select cid,'data004',data004,sendtime from ta
(select cid,'data001' datatype,data001 datas,sendtime from ta
union all
select cid,'data002',data002,sendtime from ta
union all
select cid,'data003',data003,sendtime from ta
union all
select cid,'data004',data004,sendtime from ta);
人家明显是一个人好不。
union all是必须的。
你看清了,虽然uinion了,但是确只有4个,除非你以后还有更多的data00X
的确有更多的data00x,有二十多项
cid varchar2(4),
data001 int,
data002 int,
data003 int,
data004 int,
sendtime date);insert into ta values('c001',1,2,3,4,to_date('2009-5-5 12:11:11'));
insert into ta values('c002',10,20,30,40,to_date('2009-5-5 12:11:11'));
insert into ta values('c003',11,22,33,44,to_date('2009-5-5 12:11:11'));
create table tatype
(
datatype varchar2(10)
);insert into tatype values('data001');
insert into tatype values('data002');
insert into tatype values('data003');
insert into tatype values('data004');create view v_ta (cid, datatype, datas, sendtime) as
select cid, datatype,
decode(datatype,
'data001',data001,
'data002',data002,
'data003',data003,
'data004',data004) datas, sendtime
from ta, tatype;
(select cid,'data001' datatype,data001 datas,sendtime from ta
union all
select cid,'data002',data002,sendtime from ta
union all
select cid,'data003',data003,sendtime from ta
union all
select cid,'data004',data004,sendtime from ta);