如何将以下数据格式的数据转换为 初始化格式如下: 1 市政府 房产 1 2 3 2 省政府 房产 4 3 4 3 肥东 房产 5 4 5 4 肥西 房产 6 5 7 5 长风 房产 7 7 8 6 淮南 房产 8 9 5 7 市政府 汽车 1 2 3 8 省政府 汽车 4 3 4 9 肥东 汽车 5 4 5 10 肥西 汽车 6 5 7 11 长风 汽车 7 7 8 12 淮南 汽车 8 9 5 13 肥西 奢侈品 6 5 7 14 长风 奢侈品 7 7 8 15 淮南 奢侈品 8 9 5 16 市政府 贵重首饰 1 2 3 17 省政府 贵重首饰 4 3 4 18 肥东 贵重首饰 5 4 5
需要转变为
房产 汽车 奢侈品 奢侈品 PRO_TYPE 场次 销售价 佣金 场次 销售价 佣金 场次 销售价 佣金 场次 销售价 佣金 1 淮南 8 9 5 8 9 5 8 9 5 2 肥东 5 4 5 5 4 5 5 4 5 3 市政府 1 2 3 1 2 3 1 2 3 4 省政府 4 3 4 4 3 4 4 3 4 5 肥西 6 5 7 6 5 7 6 5 7 6 长风 7 7 8 7 7 8 7 7 8
处理sql语句如下: select pro_type, sum(decode(bm_tyoe, '房产', jiage1)) 房产场次, sum(decode(bm_tyoe, '房产', jiage2)) 房产销售价, sum(decode(bm_tyoe, '房产', jiage3)) 房产佣金, sum(decode(bm_tyoe, '汽车', jiage1)) 汽车场次, sum(decode(bm_tyoe, '汽车', jiage2)) 房产销售价, sum(decode(bm_tyoe, '汽车', jiage3)) 汽车佣金, sum(decode(bm_tyoe, '奢侈品', jiage1)) 奢侈品场次, sum(decode(bm_tyoe, '奢侈品', jiage2)) 奢侈品销售价, sum(decode(bm_tyoe, '奢侈品', jiage3)) 奢侈品佣金, sum(decode(bm_tyoe, '贵重首饰', jiage1)) 贵重首饰场次, sum(decode(bm_tyoe, '贵重首饰', jiage2)) 贵重首饰销售价, sum(decode(bm_tyoe, '贵重首饰', jiage3)) 贵重首饰佣金 from tmp_table group by pro_type prompt PL/SQL Developer import file
prompt Created on 2012年7月5日 by jiaorg
set feedback off
set define off
prompt Creating TMP_TABLE...
create table TMP_TABLE
(
PRO_TYPE VARCHAR2(20),
BM_TYOE VARCHAR2(20),
JIAGE1 NUMBER(5),
JIAGE2 NUMBER(5),
JIAGE3 NUMBER(5)
)
tablespace ABRES
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
prompt Disabling triggers for TMP_TABLE...
alter table TMP_TABLE disable all triggers;
prompt Deleting TMP_TABLE...
delete from TMP_TABLE;
commit;
prompt Loading TMP_TABLE...
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('市政府', '房产', 1, 2, 3);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('省政府', '房产', 4, 3, 4);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥东', '房产', 5, 4, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥西', '房产', 6, 5, 7);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('长风', '房产', 7, 7, 8);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('淮南', '房产', 8, 9, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('市政府', '汽车', 1, 2, 3);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('省政府', '汽车', 4, 3, 4);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥东', '汽车', 5, 4, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥西', '汽车', 6, 5, 7);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('长风', '汽车', 7, 7, 8);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('淮南', '汽车', 8, 9, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥西', '奢侈品', 6, 5, 7);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('长风', '奢侈品', 7, 7, 8);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('淮南', '奢侈品', 8, 9, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('市政府', '贵重首饰', 1, 2, 3);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('省政府', '贵重首饰', 4, 3, 4);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥东', '贵重首饰', 5, 4, 5);
commit;
prompt 18 records loaded
prompt Enabling triggers for TMP_TABLE...
alter table TMP_TABLE enable all triggers;
set feedback on
set define on
prompt Done.
|