假定今天是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,谁知道怎么办?
解决方案 »
- oarcle重建索引时锁表报的错误号
- 控制文件的备份!
- Oracle存储过程 返回一个集合(列表)?
- 如何写SQL语句查询任一月份分数不超过100,并且连续三个月不超过270这样的语句
- 如何将触发器权限授予另一个用户
- 求教:用varchar(8)还是Number(8)作主键,效率如何?
- oci问题
- 大家快帮帮我,在线等:用户的机器的服务里没有“OracleTNSListener80”
- PROCESS过多问题。
- 滴~求助卡,Oracle合并两个表中的相同字段,其余全部展示,sql
- 小弟初学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