SQL> SQL> select b.rn,nvl(a.call_times,0) call_times,nvl(a.DURATION_MIN,0) DURATION_MIN,nvl(a.CLEARING_FEE,0) CLEARING_FEE 2 from ttt1 a,(select 'NM'||lpad(rownum,2,'0') rn from dual connect by rownum<=(select max(to_number(substr(cell_id,3)))-1 from ttt1)) b 3 where b.rn=a.cell_id(+) 4 ;
SQL> select b.rn,nvl(a.call_times,0) call_times,nvl(a.DURATION_MIN,0) DURATION_MIN,nvl(a.CLEARING_FEE,0) CLEARING_FEE 2 from ttt1 a,(select 'NM'||lpad(rownum,2,'0') rn from dual connect by rownum<=(select max(to_number(substr(cell_id,3)))-1 from ttt1)) b 3 where b.rn=a.cell_id(+) 4 order by b.rn 5 ;
1:生成一个含有NM01---NM20的表,然后通过全连接或者左连接来求出
2:生成一个含有NM01---NM20的每条记录的各列为0的表,然后union all后分组统计即可
最直接的做法就是第一个,产生一个NM01---NM20的表,然后和原表根据此编号进行外连接,并且用nvl函数处理不存在时候为0
select rownum from dual connect by rownum <= 10;你可以如法炮制。
Connected as scott
SQL> select * from ttt1;
CELL_ID CALL_TIMES DURATION_MIN CLEARING_FEE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NM01 2346885 3858150 231475.855
NM02 8349 15767 189.204
NM03 718247 1563142 93775.074
NM07 12473780 26016211 26015.879
NM08 29477 70799 4247.94
NM09 10502623 38139852 2288356.338
NM12 66129 175832 10549.92
NM13 7208 11844 710.586
NM15 121 492 29.298
NM19 731 1222 0
NM20 298 827 0
11 rows selected
SQL>
SQL> select b.rn,nvl(a.call_times,0) call_times,nvl(a.DURATION_MIN,0) DURATION_MIN,nvl(a.CLEARING_FEE,0) CLEARING_FEE
2 from ttt1 a,(select 'NM'||lpad(rownum,2,'0') rn from dual connect by rownum<=(select max(to_number(substr(cell_id,3)))-1 from ttt1)) b
3 where b.rn=a.cell_id(+)
4 ;
RN CALL_TIMES DURATION_MIN CLEARING_FEE
------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NM01 2346885 3858150 231475.855
NM02 8349 15767 189.204
NM03 718247 1563142 93775.074
NM07 12473780 26016211 26015.879
NM08 29477 70799 4247.94
NM09 10502623 38139852 2288356.338
NM12 66129 175832 10549.92
NM13 7208 11844 710.586
NM15 121 492 29.298
NM19 731 1222 0
NM20 298 827 0
NM05 0 0 0
NM16 0 0 0
NM06 0 0 0
NM10 0 0 0
NM17 0 0 0
NM14 0 0 0
NM11 0 0 0
NM04 0 0 0
NM18 0 0 0
20 rows selected
SQL>
2 from ttt1 a,(select 'NM'||lpad(rownum,2,'0') rn from dual connect by rownum<=(select max(to_number(substr(cell_id,3)))-1 from ttt1)) b
3 where b.rn=a.cell_id(+)
4 order by b.rn
5 ;
RN CALL_TIMES DURATION_MIN CLEARING_FEE
------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NM01 2346885 3858150 231475.855
NM02 8349 15767 189.204
NM03 718247 1563142 93775.074
NM04 0 0 0
NM05 0 0 0
NM06 0 0 0
NM07 12473780 26016211 26015.879
NM08 29477 70799 4247.94
NM09 10502623 38139852 2288356.338
NM10 0 0 0
NM11 0 0 0
NM12 66129 175832 10549.92
NM13 7208 11844 710.586
NM14 0 0 0
NM15 121 492 29.298
NM16 0 0 0
NM17 0 0 0
NM18 0 0 0
NM19 731 1222 0
NM20 298 827 0
20 rows selected
SQL>
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 (
3 select 'NM01' CELL_ID,2346885 CALL_TIMES,3858150 DURATION_MIN,231475.855 CLEARING_FEE from dual
4 union all
5 select 'NM02',8349,15767,189.204 from dual union all
6 select 'NM03',718247,1563142,93775.074 from dual union all
7 select 'NM07',12473780,26016211,26015.879 from dual union all
8 select 'NM08',29477,70799,4247.94 from dual union all
9 select 'NM09',10502623,38139852,2288356.338 from dual union all
10 select 'NM12',66129,175832,10549.92 from dual union all
11 select 'NM13',7208,11844,710.586 from dual union all
12 select 'NM15',121,492,29.298 from dual union all
13 select 'NM19',731,1222,0 from dual union all
14 select 'NM20',298,827,0 from dual)
15 select nvl(a.CELL_ID,b.CELL_ID) CELL_ID,nvl(a.CALL_TIMES,0) CALL_TIMES,
16 nvl(a.DURATION_MIN,0) DURATION_MIN,
17 nvl(a.CLEARING_FEE,0) CLEARING_FEE
18 from tb a full join (select 'NM'||lpad(level,2,0) CELL_ID from dual
19 connect by rownum<=(select max(substr(CELL_ID,-2)) from tb) )b
20 on a.CELL_ID=b.CELL_ID
21* order by CELL_ID
SQL> /CELL_I CALL_TIMES DURATION_MIN CLEARING_FEE
------ ---------- ------------ ------------
NM01 2346885 3858150 231475.855
NM02 8349 15767 189.204
NM03 718247 1563142 93775.074
NM04 0 0 0
NM05 0 0 0
NM06 0 0 0
NM07 12473780 26016211 26015.879
NM08 29477 70799 4247.94
NM09 10502623 38139852 2288356.34
NM10 0 0 0
NM11 0 0 0CELL_I CALL_TIMES DURATION_MIN CLEARING_FEE
------ ---------- ------------ ------------
NM12 66129 175832 10549.92
NM13 7208 11844 710.586
NM14 0 0 0
NM15 121 492 29.298
NM16 0 0 0
NM17 0 0 0
NM18 0 0 0
NM19 731 1222 0
NM20 298 827 0已选择20行。