先生成一个有日期的表,再去跟实际表连接,如下:with t as( select to_date('2012-6-1','yyyy-mm-dd') d from dual union all select to_date('2012-6-2','yyyy-mm-dd') d from dual union all select to_date('2012-6-3','yyyy-mm-dd') d from dual union all select to_date('2012-6-4','yyyy-mm-dd') d from dual union all select to_date('2012-6-5','yyyy-mm-dd') d from dual union all select to_date('2012-6-6','yyyy-mm-dd') d from dual union all select to_date('2012-6-7','yyyy-mm-dd') d from dual union all select to_date('2012-6-8','yyyy-mm-dd') d from dual union all select to_date('2012-6-9','yyyy-mm-dd') d from dual union all select to_date('2012-6-10','yyyy-mm-dd') d from dual ), ts as( select to_date('2012-6-3','yyyy-mm-dd') d,12 nu from dual union all select to_date('2012-6-4','yyyy-mm-dd'), 123 from dual ) select t.d,nvl(ts.nu,0) from t left join ts on t.d=ts.d order by t.d; --结果 2012-6-1 0 2012-6-2 0 2012-6-3 12 2012-6-4 123 2012-6-5 0 2012-6-6 0 2012-6-7 0 2012-6-8 0 2012-6-9 0 2012-6-10 0
--t为你的表,其中最早日期,最后日期为两个变量,即为你的2012年 6月1号到6月10号 select t1.rn,t.... (select rownum rn from dba_tables where rownum<(最后日期-最早日期+1)) t1 left join t where t1.rn=(t.当前日前-最早日期+1)
sql 大概如下:select sum(count) cnt, to_char(createtime,'dd/mm/yy') day from usagerecord where createtime between ? and ? group by to_char(createtime,'dd/mm/yy');
构造日期表关联你的表 with t1 as ( select date'2012-06-03' t_date from dual union all select date'2012-06-03' t_date from dual union all select date'2012-06-04' t_date from dual )select c1,count(t_date) c2 from ( select date'2012-06-01'+level-1 c1 from dual connect by level < 11 ) t2 left join t1 on t2.c1 = t1.t_date group by c1 order by c1 c1 c2 ---------------------------------- 1 2012/6/1 0 2 2012/6/2 0 3 2012/6/3 2 4 2012/6/4 1 5 2012/6/5 0 6 2012/6/6 0 7 2012/6/7 0 8 2012/6/8 0 9 2012/6/9 0 10 2012/6/10 0
如果是动态的话 那将2个时间作为参数select c1,count(t_date) c2 from ( select date'2012-06-01'+level-1 c1 from dual connect by level < date'2012-06-10'-date'2012-06-01'+2 ) t2 left join t1 on t2.c1 = t1.t_date group by c1 order by c1
count(t_date) 干嘛, 没懂啊... 能基于我这个例子改一下吗?select sum(count) cnt, to_char(createtime,'dd/mm/yy') day from usagerecord where createtime between ? and ? group by to_char(createtime,'dd/mm/yy');
构造日期表,再与基表做连接 with temp as ( select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-02', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-04', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-04', 'yyyy-mm-dd') dt from dual union all select to_date('2009-07-05', 'yyyy-mm-dd') dt from dual ) select b.dt, count(temp.dt) from ( SELECT to_date('2009-07-01', 'yyyy-mm-dd')+rownum-1 dt from dual connect by to_date('2009-07-01', 'yyyy-mm-dd')+(rownum-1) < to_date('2009-09-05', 'yyyy-mm-dd') ) b, temp where b.dt=temp.dt(+) group by b.dt order by b.dt;
这样吧 ? 其实 LZ连表字段也没说清楚 我们不好猜啊 是一个日期 对应一个访问量么 with t1 as ( select date'2012-06-03' t_date,5 t_count from dual union all select date'2012-06-04' t_date,10 from dual )select c1,nvl(sum(t_count),0) c2 from ( select date'2012-06-01'+level-1 c1 from dual connect by level < date'2012-06-10'-date'2012-06-01'+2 ) t2 left join t1 on t2.c1 = t1.t_date group by c1 order by c1
明白了,但是还有个问题,下面这段是抓日期的,精准到天, 我想精准到月,怎么搞. 例如看1月到10月的.?多谢了. select date'2012-06-01'+level-1 c1 from dual connect by level < date'2012-06-10'-date'2012-06-01'+2
with t1 as ( select date'2012-06-03' t_date,5 t_count from dual union all select date'2012-06-04' t_date,10 from dual )select to_char(t2.c1,'yyyy-mm') c1,nvl(sum(t_count),0) c2 from ( select add_months(date'2012-01-01',level-1) c1 from dual connect by level < round(months_between(date'2012-10-10',date'2012-01-01'),0)+2 ) t2 left join t1 on to_char(t2.c1,'yyyymm') = to_char(t1.t_date,'yyyymm') group by c1 order by c1 c1 c2 ---------------------- 1 2012-01 0 2 2012-02 0 3 2012-03 0 4 2012-04 0 5 2012-05 0 6 2012-06 15 7 2012-07 0 8 2012-08 0 9 2012-09 0 10 2012-10 0
select to_date('2012-6-1','yyyy-mm-dd') d from dual
union all select to_date('2012-6-2','yyyy-mm-dd') d from dual
union all select to_date('2012-6-3','yyyy-mm-dd') d from dual
union all select to_date('2012-6-4','yyyy-mm-dd') d from dual
union all select to_date('2012-6-5','yyyy-mm-dd') d from dual
union all select to_date('2012-6-6','yyyy-mm-dd') d from dual
union all select to_date('2012-6-7','yyyy-mm-dd') d from dual
union all select to_date('2012-6-8','yyyy-mm-dd') d from dual
union all select to_date('2012-6-9','yyyy-mm-dd') d from dual
union all select to_date('2012-6-10','yyyy-mm-dd') d from dual
), ts as(
select to_date('2012-6-3','yyyy-mm-dd') d,12 nu from dual
union all select to_date('2012-6-4','yyyy-mm-dd'), 123 from dual
)
select t.d,nvl(ts.nu,0) from t left join ts on t.d=ts.d order by t.d;
--结果
2012-6-1 0
2012-6-2 0
2012-6-3 12
2012-6-4 123
2012-6-5 0
2012-6-6 0
2012-6-7 0
2012-6-8 0
2012-6-9 0
2012-6-10 0
--t为你的表,其中最早日期,最后日期为两个变量,即为你的2012年 6月1号到6月10号
select t1.rn,t....
(select rownum rn from dba_tables
where rownum<(最后日期-最早日期+1)) t1
left join t
where t1.rn=(t.当前日前-最早日期+1)
from usagerecord
where createtime between ? and ?
group by to_char(createtime,'dd/mm/yy');
with t1 as
(
select date'2012-06-03' t_date from dual
union all
select date'2012-06-03' t_date from dual
union all
select date'2012-06-04' t_date from dual
)select c1,count(t_date) c2
from (
select date'2012-06-01'+level-1 c1
from dual
connect by level < 11
) t2 left join t1 on t2.c1 = t1.t_date
group by c1
order by c1 c1 c2
----------------------------------
1 2012/6/1 0
2 2012/6/2 0
3 2012/6/3 2
4 2012/6/4 1
5 2012/6/5 0
6 2012/6/6 0
7 2012/6/7 0
8 2012/6/8 0
9 2012/6/9 0
10 2012/6/10 0
from (
select date'2012-06-01'+level-1 c1
from dual
connect by level < date'2012-06-10'-date'2012-06-01'+2
) t2 left join t1 on t2.c1 = t1.t_date
group by c1
order by c1
能基于我这个例子改一下吗?select sum(count) cnt, to_char(createtime,'dd/mm/yy') day
from usagerecord
where createtime between ? and ?
group by to_char(createtime,'dd/mm/yy');
with temp as
(
select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-01', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-02', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-04', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-04', 'yyyy-mm-dd') dt from dual
union all
select to_date('2009-07-05', 'yyyy-mm-dd') dt from dual
)
select b.dt, count(temp.dt) from
(
SELECT to_date('2009-07-01', 'yyyy-mm-dd')+rownum-1 dt from dual connect by to_date('2009-07-01', 'yyyy-mm-dd')+(rownum-1) < to_date('2009-09-05', 'yyyy-mm-dd')
) b, temp
where b.dt=temp.dt(+)
group by b.dt
order by b.dt;
with t1 as
(
select date'2012-06-03' t_date,5 t_count from dual
union all
select date'2012-06-04' t_date,10 from dual
)select c1,nvl(sum(t_count),0) c2
from (
select date'2012-06-01'+level-1 c1
from dual
connect by level < date'2012-06-10'-date'2012-06-01'+2
) t2 left join t1 on t2.c1 = t1.t_date
group by c1
order by c1
from dual
connect by level < date'2012-06-10'-date'2012-06-01'+2
with t1 as
(
select date'2012-06-03' t_date,5 t_count from dual
union all
select date'2012-06-04' t_date,10 from dual
)select to_char(t2.c1,'yyyy-mm') c1,nvl(sum(t_count),0) c2
from (
select add_months(date'2012-01-01',level-1) c1
from dual
connect by level < round(months_between(date'2012-10-10',date'2012-01-01'),0)+2
) t2 left join t1 on to_char(t2.c1,'yyyymm') = to_char(t1.t_date,'yyyymm')
group by c1
order by c1 c1 c2
----------------------
1 2012-01 0
2 2012-02 0
3 2012-03 0
4 2012-04 0
5 2012-05 0
6 2012-06 15
7 2012-07 0
8 2012-08 0
9 2012-09 0
10 2012-10 0