如何将以下数据格式的数据转换为 初始化格式如下： 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)) 贵重首饰佣金 ｆｒｏｍ tmp_table group by pro_type
1: prompt PL/SQL Developer import file
2: prompt Created on 2012年7月5日 by jiaorg
3: set feedback off
4: set define off
5: prompt Creating TMP_TABLE...
6: create table TMP_TABLE
7: (
8: PRO_TYPE VARCHAR2(20),
9: BM_TYOE VARCHAR2(20),
10: JIAGE1 NUMBER(5),
11: JIAGE2 NUMBER(5),
12: JIAGE3 NUMBER(5)
13: )
14: tablespace ABRES
15: pctfree 10
16: initrans 1
17: maxtrans 255
18: storage
19: (
20: initial 64K
21: next 1M
22: minextents 1
23: maxextents unlimited
24: );
25:
26: prompt Disabling triggers for TMP_TABLE...
27: alter table TMP_TABLE disable all triggers;
28: prompt Deleting TMP_TABLE...
29: delete from TMP_TABLE;
30: commit;
31: prompt Loading TMP_TABLE...
32: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
33: values ('市政府', '×××', 1, 2, 3); 34: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
35: values ('省政府', '×××', 4, 3, 4); 36: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
37: values ('肥东', '×××', 5, 4, 5); 38: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
39: values ('肥西', '×××', 6, 5, 7); 40: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
41: values ('长风', '×××', 7, 7, 8); 42: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
43: values ('淮南', '×××', 8, 9, 5); 44: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
45: values ('市政府', '汽车', 1, 2, 3); 46: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
47: values ('省政府', '汽车', 4, 3, 4); 48: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
49: values ('肥东', '汽车', 5, 4, 5); 50: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
51: values ('肥西', '汽车', 6, 5, 7); 52: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
53: values ('长风', '汽车', 7, 7, 8); 54: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
55: values ('淮南', '汽车', 8, 9, 5); 56: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
57: values ('肥西', '奢侈品', 6, 5, 7); 58: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
59: values ('长风', '奢侈品', 7, 7, 8); 60: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
61: values ('淮南', '奢侈品', 8, 9, 5); 62: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
63: values ('市政府', '贵重首饰', 1, 2, 3); 64: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
65: values ('省政府', '贵重首饰', 4, 3, 4); 66: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
67: values ('肥东', '贵重首饰', 5, 4, 5); 68: commit;
69: prompt 18 records loaded
70: prompt Enabling triggers for TMP_TABLE...
71: alter table TMP_TABLE enable all triggers;
72: set feedback on
73: set define on
74: prompt Done.
欢迎访问我的独立blog: