请教oracle分组统计的问题!!!

Question

你上面的语句条件:
select to_char(b.cre_date,'yyyymmdd'),count(*)
 from tos_product_order b
 where to_char(b.cre_date,'yyyymm') = '200309'
 group by to_char(b.cre_date,'yyyymmdd')
中的where字句 是限制了订单的日期吗?去掉就应该满足你的统计要求了吧!

Answer 1

NVL()
select
 NVL(select to_char(b.cre_date,'yyyymmdd'),count(*)
 from tos_product_order b
 where to_char(b.cre_date,'yyyymm') = '200309'
 group by to_char(b.cre_date,'yyyymmdd'),
 0)
from dual;

Answer 2

select dt,nvl(c,0) 
from (
select  to_char(b.cre_date,'yyyymmdd') dt,count(*) c 
 from  tos_product_order  b  
 where  to_char(b.cre_date,'yyyymm')  =  '200309'  
 group  by  to_char(b.cre_date,'yyyymmdd')  
) t,(select to_date('200309','yyyymm')+rownum-1 rdt,0 c1
    from user_objects 
where rownum<=to_char(last_day(to_date('200309','yyyymm')),'dd')
) tt
where tt.edt=tt.dt(+)

Answer 3

bzszp(www.bzszp.533.net) 
好办法:).

Answer 4

bzszp(www.bzszp.533.net) 
你未测试:)
好像有点问题

Answer 5

13:35:09 SQL> select * from tbdemo;CRE_DATE
-------------------
2003-09-01 00:00:00
2003-09-02 00:00:00
2003-09-02 00:00:00
2003-09-03 00:00:00
2003-09-06 00:00:00
2003-09-06 00:00:00已选择6行。已用时间:  00: 00: 00.94
13:37:06 SQL> select rdt,nvl(c,0) 
13:37:44   2  from (
13:37:50   3  select  to_char(b.cre_date,'yyyymmdd') dt,count(*) c 
13:37:50   4   from  tbdemo  b
13:37:50   5   where  to_char(b.cre_date,'yyyymm')  =  '200309'  
13:37:50   6   group  by  to_char(b.cre_date,'yyyymmdd') 
13:37:50   7  ) t,(select to_date('200309','yyyymm')+rownum-1 rdt,0 c1
13:37:50   8  from all_source
13:37:50   9  where rownum<=to_char(last_day(to_date('200309','yyyymm')),'dd')
13:37:51  10  ) tt
13:37:51  11  where tt.rdt=t.dt(+);RDT                   NVL(C,0)
------------------- ----------
2003-09-01 00:00:00          1
2003-09-02 00:00:00          2
2003-09-03 00:00:00          1
2003-09-04 00:00:00          0
2003-09-05 00:00:00          0
2003-09-06 00:00:00          2
2003-09-07 00:00:00          0
2003-09-08 00:00:00          0
2003-09-09 00:00:00          0
2003-09-10 00:00:00          0
2003-09-11 00:00:00          0RDT                   NVL(C,0)
------------------- ----------
2003-09-12 00:00:00          0
2003-09-13 00:00:00          0
2003-09-14 00:00:00          0
2003-09-15 00:00:00          0
2003-09-16 00:00:00          0
2003-09-17 00:00:00          0
2003-09-18 00:00:00          0
2003-09-19 00:00:00          0
2003-09-20 00:00:00          0
2003-09-21 00:00:00          0
2003-09-22 00:00:00          0RDT                   NVL(C,0)
------------------- ----------
2003-09-23 00:00:00          0
2003-09-24 00:00:00          0
2003-09-25 00:00:00          0
2003-09-26 00:00:00          0
2003-09-27 00:00:00          0
2003-09-28 00:00:00          0
2003-09-29 00:00:00          0
2003-09-30 00:00:00          0已选择30行。已用时间:  00: 00: 04.63
13:37:56 SQL> 

Answer 6

用group by 只能统计存在数值与null的数据,不存在的记录不会被列出来。
做一个函数实现

Answer 7

bzszp(www.bzszp.533.net)
问题是,当某一天有数据出现的时候.这个SQL就有问题了.

Answer 8

我这儿居然报错:SQL> 
SQL> select rdt,nvl(c,0)
  2    from (
  3    select  to_char(b.rbrq,'yyyymmdd') dt,count(*) c
  4      from  p_zjrb  b
  5     where  to_char(b.rbrq,'yyyymm')  =  '200307'
  6     group  by  to_char(b.rbrq,'yyyymmdd')
  7    ) t,(select to_date('200307','yyyymm')+rownum-1 rdt,0 c1
  8    from all_source
  9    where rownum<=to_char(last_day(to_date('200307','yyyymm')),'dd')
 10    ) tt
 11    where tt.rdt=t.dt(+)
 12  ;select rdt,nvl(c,0)
  from (
  select  to_char(b.rbrq,'yyyymmdd') dt,count(*) c
    from  p_zjrb  b
   where  to_char(b.rbrq,'yyyymm')  =  '200307'
   group  by  to_char(b.rbrq,'yyyymmdd')
  ) t,(select to_date('200307','yyyymm')+rownum-1 rdt,0 c1
  from all_source
  where rownum<=to_char(last_day(to_date('200307','yyyymm')),'dd')
  ) tt
  where tt.rdt=t.dt(+)ORA-01861: 文字与格式字符串不匹配

Answer 9

本人作了一下修改.这样应该是最正确了:)select rdt,nvl(c,0) ,c
  from (
  select  to_char(b.rbrq,'yyyymmdd') dt,count(*) c 
    from  p_zjrb  b
   where  to_char(b.rbrq,'yyyymm')  =  '200307'  
   group  by  to_char(b.rbrq,'yyyymmdd') 
  ) t,(select to_char(to_date('200307','yyyymm')+rownum-1,'yyyymmdd') rdt,0 c1
  from all_source
  where rownum<=to_char(last_day(to_date('200307','yyyymm')),'dd')
  ) tt
  where tt.rdt=t.dt(+)

Answer 10

與此類似：SQL> SELECT SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4')) FROM BAO_TEMP;SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4'))
------------------------------------------------------------1884
1884SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4'))
------------------------------------------------------------
選取了 18 列SQL> SELECT NVL(SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4')),0) FROM BAO_TEMP
  2  /NVL(SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4')),0)
------------------------------------------------------------
0
1884
0
0
0
0
0
0
0
0
1884NVL(SUBSTR(BASE_ID,1,INSTR(BASE_ID,'4')),0)
------------------------------------------------------------
0
0
0
0
0
0
0選取了 18 列