现有表 A
内有一 Date 字段 dt要求:给定起始日期和终止日期, 要查出这两个日期区间内所有连续日期的记录数量如:
数据
dt
2009-07-01
2009-07-01
2009-07-01
2009-07-02
2009-07-04
2009-07-04
2009-07-05给定条件 起始日期:2009-07-01 终止日期:2009-07-05那么结果应当是
2009-07-01 3
2009-07-02 1
2009-07-03 0 --dt字段为这个日期的没有记录
2009-07-04 2
2009-07-05 1
向众高手高分求救了 -------------
给分500, 因发不了高于100分的帖子,会开贴追加
内有一 Date 字段 dt要求:给定起始日期和终止日期, 要查出这两个日期区间内所有连续日期的记录数量如:
数据
dt
2009-07-01
2009-07-01
2009-07-01
2009-07-02
2009-07-04
2009-07-04
2009-07-05给定条件 起始日期:2009-07-01 终止日期:2009-07-05那么结果应当是
2009-07-01 3
2009-07-02 1
2009-07-03 0 --dt字段为这个日期的没有记录
2009-07-04 2
2009-07-05 1
向众高手高分求救了 -------------
给分500, 因发不了高于100分的帖子,会开贴追加
select dt, count(*) from a group by dt
select dt, count(*) from a where dt between '2009-07-01' and '2009-07-05' group by dt
from (SELECT begin_date + level -1 dt FROM test connect BY begin_date + level -1 <= end_date) t,
A a
where t.dt = a.dt(+)
group by t.dt;
那么稍微修改下,试试吧,在家没法测试,应该差不多
select t.dt, count(1)
from (
select to_date('2009-07-01', 'yyyy-mm-dd') - 1 + rownum dt
from dual
connect by rownum <= to_date('2009-07-05', 'yyyy-mm-dd')
- to_date('2009-07-01', 'yyyy-mm-dd')
) t,
A a
where t.dt = a.dt(+)
group by t.dt;
select t.dt, count(1)
from (select to_date('2009-07-01', 'yyyy-mm-dd') - 1 + rownum dt
from all_objects
where rownum <= to_date('2009-07-05', 'yyyy-mm-dd')
- to_date('2009-07-01', 'yyyy-mm-dd')
+ 1
) t,
A a
where t.dt = a.dt(+)
group by t.dt;
#7 all_objects 记录数不定,有可能存在 需要生成的日期数量>all_objects记录数 的情况
2 select to_date('2009-07-01', 'yyyy-mm-dd') - 1 + rownum dt
3 from (select 1 from dual group by cube(1,2,3,4,5,6,7))
4 ) b, a where a.dt(+) = b.dt
5 and b.dt between to_date('2009-07-01', 'YYYY-MM-DD') and to_date('2009-07-05', 'YYYY-MM-DD')
6 group by b.dt
7 order by b.dt;DT COUNT(A.DT)
-------------- -----------
01-7月 -09 3
02-7月 -09 1
03-7月 -09 0
04-7月 -09 2
05-7月 -09 1如果想大点儿,就CUBE多点,再多也能装下
(SELECT to_date('2009-01-01','yyyy-mm-dd')+ROWNUM DT FROM DUAL
CONNECT BY LEVEL<=365) B,A
WHERE B.DT=A.DT(+) AND B.DT BETWEEN to_date('2009-07-01','yyyy-mm-dd') and
to_date('2009-07-05','yyyy-mm-dd')
GROUP BY B.DT
ORDER BY DT结果:
DT CNT
2009-7-1 3
2009-7-2 1
2009-7-3 0
2009-7-4 2
2009-7-5 1
(
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-07-05', 'yyyy-mm-dd')) b, temp where b.dt=temp.dt(+) group by b.dt order by b.dt;