结果按编号输出，不存在的补0。

Question

本帖最后由 zzh8845 于 2010-10-21 20:34:45 编辑

Answer 1

常用做法：
1：生成一个含有NM01---NM20的表,然后通过全连接或者左连接来求出
2：生成一个含有NM01---NM20的每条记录的各列为0的表,然后union all后分组统计即可

Answer 2

最直接的做法就是第一个，产生一个NM01---NM20的表，然后和原表根据此编号进行外连接，并且用nvl函数处理不存在时候为0

Answer 3

找到最大的 和最小的，用connect by构造之间的。比如构造1-10可以用
select rownum from dual connect by rownum <= 10;你可以如法炮制。

Answer 4

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
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> 

Answer 5

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  ;
 
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> 

Answer 6

这种一般都可以先使用connect by构造全集,然后外连接处理.

Answer 7

用递归加完全外连接
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行。