现在遇到一个问题,如下:
我想查询 2012年 6月1号到6月10号的页面访问量, 但是只有3号和4号有数据,意味着只能查询出来2条记录,我怎么可以把没有数据的日期的访问量设置为0,这样显示10条记录呢?
请大家多多帮忙....
我想查询 2012年 6月1号到6月10号的页面访问量, 但是只有3号和4号有数据,意味着只能查询出来2条记录,我怎么可以把没有数据的日期的访问量设置为0,这样显示10条记录呢?
请大家多多帮忙....
解决方案 »
- OracleDBConsoleorcl无法启动,且提示错误代码2
- Merge, connect by 语句的意思
- oracle的gruppo by 问题
- Oracle 数据库 (OLE DB) 不支持关键字: “provider”的问题
- 参数修改的问题,请大家帮忙看看
- oracle字符串为空判断
- 请问哪里有oracle developer2000的下载
- 高手请进!!!!!关于ORACLE中的sql语句包含NOT EXISTS的问题
- 如果数据库需要存多种语言,应该设什么字符集
- 眼泪求救,oracle 11g 导出的dmp无法导入9i
- 有什么工具可以获取oracle表结构说明呀?
- 急~~~~~查询各公司的一条记录
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