with a as( --目标表a
select sysdate-9 at from dual union all --不在一周内的时间
select sysdate-10 from dual union all
select sysdate-6 at from dual union all --在一周内的时间
select sysdate-6 from dual union all
select sysdate-5 from dual union all
select sysdate-4 from dual union all
select sysdate-3 from dual union all
select sysdate-3 from dual union all
select sysdate-3 from dual union all
select sysdate-2 from dual union all
select sysdate-1 from dual union all
select sysdate from dual
)
select bt as "时间",count(1) as "产生记录条数" from a,(select to_char(sysdate-level+1,'yyyy-MM-dd') bt from dual connect by level <= 7) b
where to_char(a.at,'yyyy-MM-dd') = b.bt group by bt;