按每日统计，没有这一日的也要显示，为0

Question

表为
name          date
a            2006/02/03
b            2006/02/04
c            2006/02/05
d            2006/02/07
e            2006/02/04要统计成
日              次数
2006/02/01      0
2006/02/02      0
2006/02/03      1
2006/02/04      2
2006/02/05      1
2006/02/06      0
2006/02/07      1
......
2006/02/30      0用select date, count(*) as flux from 表统计出来的只有表中有这一天的数据，没有的怎么办呢？

Answer 1

建议楼主建一个日期表,以便一一对应然后执行以下语句
create table mydate(
  rq varchar2(10)
);SQL> begin
  2    for i in 1..10000 loop
  3      insert into mydate values to_char(sysdate+i,'yyyy/mm/dd');
  4    end loop;
  5  end;
  6  /最后,用子查询
select rq,
(select count(*) from yourtable where yourtable.rq=mydate.rq) flux
 from mydate where rq<='2006/06/01';

Answer 2

错了,是
create table mydate(
rq varchar2(10));begin
  for i in 0..10000 loop
    insert into mydate values (to_char((sysdate+i),'yyyy/mm/dd'));
  end loop;
end;
/select rq,
(select count(*) from yourtable where yourtable.rq=mydate.rq) flux
 from mydate where rq<='2006/06/01';

Answer 3

楼主需要显示的日期范围总是知道的吧，下面这段演示了显示2.1到2.28之间的数据，有兴趣可以好好研究一下，其中是可以通过结束日期减开始日期得到的！！SQL> create table datetest
  2  ( name varchar2(10), datefield date);Table createdSQL> select * from datetest;NAME       DATEFIELD
---------- -----------SQL> insert into datetest(name,datefield) values('a',to_date('2006-02-03','yyyy-mm-dd'));1 row insertedSQL> insert into datetest(name,datefield) values('b',to_date('2006-02-04','yyyy-mm-dd'));1 row insertedSQL> insert into datetest(name,datefield) values('c',to_date('2006-02-05','yyyy-mm-dd'));1 row insertedSQL> insert into datetest(name,datefield) values('d',to_date('2006-02-07','yyyy-mm-dd'));1 row insertedSQL> insert into datetest(name,datefield) values('e',to_date('2006-02-04','yyyy-mm-dd'));1 row insertedSQL> select * from datetest;NAME       DATEFIELD
---------- -----------
a          2006-2-3
b          2006-2-4
c          2006-2-5
d          2006-2-7
e          2006-2-4SQL> 
SQL> select a.datefield,count(datetest.datefield)
  2  from
  3  (
  4      select to_date('2006-02-01','yyyy-mm-dd') + rownum - 1 as datefield
  5      from user_tab_cols
  6      where rownum <= 28
  7  ) A left join datetest on a.datefield = datetest.datefield
  8  group by A.datefield
  9  order by A.datefield;DATEFIELD   COUNT(DATETEST.DATEFIELD)
----------- -------------------------
2006-2-1                            0
2006-2-2                            0
2006-2-3                            1
2006-2-4                            2
2006-2-5                            1
2006-2-6                            0
2006-2-7                            1
2006-2-8                            0
2006-2-9                            0
2006-2-10                           0
2006-2-11                           0
2006-2-12                           0
2006-2-13                           0
2006-2-14                           0
2006-2-15                           0
2006-2-16                           0
2006-2-17                           0
2006-2-18                           0
2006-2-19                           0
2006-2-20                           0DATEFIELD   COUNT(DATETEST.DATEFIELD)
----------- -------------------------
2006-2-21                           0
2006-2-22                           0
2006-2-23                           0
2006-2-24                           0
2006-2-25                           0
2006-2-26                           0
2006-2-27                           0
2006-2-28                           028 rows selectedSQL> 

Answer 4

对不起没看一楼，要知道这个月到几号也很方便的嘛！！SQL> select a.datefield,count(datetest.datefield)
  2  from
  3  (
  4      select to_date('2006-02-01','yyyy-mm-dd') + rownum - 1 as datefield
  5      from user_tab_cols
  6      where rownum <= 31
  7  ) A left join datetest on a.datefield = datetest.datefield
  8  where to_char(A.datefield,'yyyymm') = '200602'
  9  group by A.datefield
 10  order by A.datefield;DATEFIELD   COUNT(DATETEST.DATEFIELD)
----------- -------------------------
2006-2-1                            0
2006-2-2                            0
2006-2-3                            1
2006-2-4                            2
2006-2-5                            1
2006-2-6                            0
2006-2-7                            1
2006-2-8                            0
2006-2-9                            0
2006-2-10                           0
2006-2-11                           0
2006-2-12                           0
2006-2-13                           0
2006-2-14                           0
2006-2-15                           0
2006-2-16                           0
2006-2-17                           0
2006-2-18                           0
2006-2-19                           0
2006-2-20                           0DATEFIELD   COUNT(DATETEST.DATEFIELD)
----------- -------------------------
2006-2-21                           0
2006-2-22                           0
2006-2-23                           0
2006-2-24                           0
2006-2-25                           0
2006-2-26                           0
2006-2-27                           0
2006-2-28                           028 rows selectedSQL>

Answer 5

算法：
select b.月日,a.次数 from
 (select date ,coint(*) as 次数
 from 表
 group by 月份字字段) a,
 (select 月日 as 次数
 from dual
 ) b
 where b.月日=a.date(*)

Answer 6

修正：
 where b.月日=a.date(*)
==〉
 where b.月日=a.date(+)

Answer 7

求本月的sql：
 select trunc(sysdate,'mm')+rownum-1 all_day from all_objects
  where rownum<=to_char(last_day(sysdate),'dd')

Answer 8

呵呵，这个需求还有点意思：
SQL> create table test_1 (a date,b number);Table createdExecuted in 0.19 secondsSQL> insert into test_1 values (sysdate,1);1 row insertedExecuted in 0.01 secondsSQL> insert into test_1 values (sysdate+20,3);1 row insertedExecuted in 0.01 seconds
SQL> insert into test_1 values (trunc(sysdate),2);1 row insertedExecuted in 0.02 secondsSQL> commit;Commit completeExecuted in 0.02 secondsSQL> select * from test_1;A                    B
----------- ----------
2006-5-8             1
2006-5-28            3
2006-5-8             2Executed in 0.141 secondsSQL> 
SQL> Select b.date1,count(a.b) From test_1 a,
  2  (Select trunc(last_day(add_months(Sysdate,-1)))+Rownum  As date1 From user_objects Where Rownum<=
  3  (Select last_day(Sysdate)-last_day(add_months(Sysdate,-1))  From dual)) b
  4  Where b.date1=a.a(+)
  5  Group By b.date1
  6  /DATE1       COUNT(A.B)
----------- ----------
2006-5-1             0
2006-5-2             0
2006-5-3             0
2006-5-4             0
2006-5-5             0
2006-5-6             0
2006-5-7             0
2006-5-8             2
2006-5-9             0
2006-5-10            0
2006-5-11            0
2006-5-12            0
2006-5-13            0
2006-5-14            0
2006-5-15            0
2006-5-16            0
2006-5-17            0
2006-5-18            0
2006-5-19            0
2006-5-20            0DATE1       COUNT(A.B)
----------- ----------
2006-5-21            0
2006-5-22            0
2006-5-23            0
2006-5-24            0
2006-5-25            0
2006-5-26            0
2006-5-27            0
2006-5-28            1
2006-5-29            0
2006-5-30            0
2006-5-31            031 rows selectedExecuted in 0.931 seconds