create table test
(
ID NUMBER(10) not null,
type_id NUMBER(10) not null,
change_type NUMBER(10) not null,
sale_id NUMBER(10) not null,
bs_ID NUMBER(9) not null
)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1
minextents 1
maxextents unlimited
);
insert into test(id,type_id,change_type,sale_id,bs_id)
values(1001,20010,'M01',10,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1002,20011,'M02',10,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1003,20010,'M01',11,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1004,20011,'M02',11,40);
SQL> select * from test; ID TYPE_ID CHANGE_TYPE SALE_ID BS_ID
----------- ----------- ----------- ----------- ----------
1001 20010 M01 10 40
1002 20011 M02 10 40
1003 20010 M01 11 40
1004 20011 M02 11 40
ID,BS_ID还是保留之前的列,就是TYPE_ID,CHANGE_TYPE,SALE_ID字段的值插入到一个字段里面val中;不用union 和union all大家有什么好的办法;
得到如下结果:SQL> select id,to_char(type_id) val,bs_id from test union all
2 select id,change_type val,bs_id from test union all
3 select id,to_char(sale_id) val,bs_id from test order by id
4 ; ID VAL BS_ID
----------- ---------------------------------------- ----------
1001 M01 40
1001 20010 40
1001 10 40
1002 20011 40
1002 M02 40
1002 10 40
1003 M01 40
1003 20010 40
1003 11 40
1004 M02 40
1004 11 40
1004 20011 40
(
ID NUMBER(10) not null,
type_id NUMBER(10) not null,
change_type NUMBER(10) not null,
sale_id NUMBER(10) not null,
bs_ID NUMBER(9) not null
)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1
minextents 1
maxextents unlimited
);
insert into test(id,type_id,change_type,sale_id,bs_id)
values(1001,20010,'M01',10,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1002,20011,'M02',10,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1003,20010,'M01',11,40);insert into test(id,type_id,change_type,sale_id,bs_id)
values(1004,20011,'M02',11,40);
SQL> select * from test; ID TYPE_ID CHANGE_TYPE SALE_ID BS_ID
----------- ----------- ----------- ----------- ----------
1001 20010 M01 10 40
1002 20011 M02 10 40
1003 20010 M01 11 40
1004 20011 M02 11 40
ID,BS_ID还是保留之前的列,就是TYPE_ID,CHANGE_TYPE,SALE_ID字段的值插入到一个字段里面val中;不用union 和union all大家有什么好的办法;
得到如下结果:SQL> select id,to_char(type_id) val,bs_id from test union all
2 select id,change_type val,bs_id from test union all
3 select id,to_char(sale_id) val,bs_id from test order by id
4 ; ID VAL BS_ID
----------- ---------------------------------------- ----------
1001 M01 40
1001 20010 40
1001 10 40
1002 20011 40
1002 M02 40
1002 10 40
1003 M01 40
1003 20010 40
1003 11 40
1004 M02 40
1004 11 40
1004 20011 40
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货