假定今天是2011-07-13日
那么select sysdate-(level-19) as dayofmonth from dual connect by level<=312011-07-01
2011-07-02
2011-07-03
2011-07-04
2011-07-05
....................2011-07-31
我现在用
select to_char(productselltime, 'yyyy-mm-dd'), count(productCardId)
from tablename
where productselltime is not null
group by (productselltime, 'yyyy-mm-dd')
得到2011-07-02 4
2011-07-04 19
2011-07-05 1其他日期如果不有对应记录的话,就不会显示出来,我现在的需求结果是:
2011-07-01 0
2011-07-02 4
2011-07-03 0
2011-07-04 19
2011-07-05 1
2011-07-06 0
就是没有结果的自动补充该日期并且结果为0
然后我是这样写的使用right join on
select to_char(productselltime, 'yyyy-mm-dd'), count(productCardId)
from tablename
right join
(select sysdate-(level-19) as dayofmonth from dual connect by level<=31)
on to_char(productselltime, 'yyyy-mm-dd') = to_char(dayofmonth, 'yyyy-mm-dd')
where productselltime is not null
group by (productselltime, 'yyyy-mm-dd')最后结果并没有把为空的结果补0,谁知道怎么办?
那么select sysdate-(level-19) as dayofmonth from dual connect by level<=312011-07-01
2011-07-02
2011-07-03
2011-07-04
2011-07-05
....................2011-07-31
我现在用
select to_char(productselltime, 'yyyy-mm-dd'), count(productCardId)
from tablename
where productselltime is not null
group by (productselltime, 'yyyy-mm-dd')
得到2011-07-02 4
2011-07-04 19
2011-07-05 1其他日期如果不有对应记录的话,就不会显示出来,我现在的需求结果是:
2011-07-01 0
2011-07-02 4
2011-07-03 0
2011-07-04 19
2011-07-05 1
2011-07-06 0
就是没有结果的自动补充该日期并且结果为0
然后我是这样写的使用right join on
select to_char(productselltime, 'yyyy-mm-dd'), count(productCardId)
from tablename
right join
(select sysdate-(level-19) as dayofmonth from dual connect by level<=31)
on to_char(productselltime, 'yyyy-mm-dd') = to_char(dayofmonth, 'yyyy-mm-dd')
where productselltime is not null
group by (productselltime, 'yyyy-mm-dd')最后结果并没有把为空的结果补0,谁知道怎么办?
解决方案 »
- Orcale中返回自动增长主键问题
- 请问大家在写数据库文档的时候如何描述PROC啊?用什么图?用什么工具?
- 请教一个使用OCIDescribeAny函数的问题!
- linux下执行oracle的dbstart,提示Can't find init file for Database "guandadb".
- 创建存储过程报错?
- 高手们帮帮忙---触发器
- 急!在线等。监听器的问题。。。。
- OraOLEDB.Oracle.1 是什么意思?
- 基础问题
- TNS字符串的含义?我应该怎么填?
- 小弟初学ora,今天碰到了一个问题,钻进了死胡同,来这里请教大家。
- 再发新贴:关于oracle存储过程全表扫描处理百万数据的速度问题
FROM tablename
RIGHT JOIN (SELECT SYSDATE - (LEVEL - 19) AS dayofmonth FROM dual CONNECT BY LEVEL <= 31)
ON to_char(productselltime, 'yyyy-mm-dd') = to_char(dayofmonth, 'yyyy-mm-dd')
GROUP BY (dayofmonth, 'yyyy-mm-dd')
FROM tablename
RIGHT JOIN (SELECT SYSDATE - (LEVEL - 19) AS dayofmonth
FROM dual
CONNECT BY LEVEL <= 31)
ON to_char(productselltime, 'yyyy-mm-dd') =
to_char(dayofmonth, 'yyyy-mm-dd')
GROUP BY to_char(dayofmonth, 'yyyy-mm-dd');
with table_a as(
select '2011-07-01' date1 from dual
union all
select '2011-07-02' date1 from dual
union all
select '2011-07-03' date1 from dual
union all
select '2011-07-04' date1 from dual
union all
select '2011-07-05' date1 from dual
),
table_b as(
select '2011-07-02' date1,4 count1 from dual
union all
select '2011-07-04' date1,19 count1 from dual
union all
select '2011-07-05' date1,1 count1 from dual
)select a.date1,nvl(b.count1,0)
from table_a a,table_b b
where a.date1 = b.date1(+)
order by a.date1