关于统计一段时间内数据的SQL语句
问题描述：
例如如下的sql语句，我想提取的数据格式为：
20120601 时间段date >= "20120601" and date <= "20120601"内的数据
20120602 时间段date >= "20120601" and date <= "20120602"内的数据
20120603 时间段date >= "20120601" and date <= "20120603"内的数据
。。。
20120630 时间段date >= "20120601" and date <= "20120630"内的数据

不想手动一条条修改日期，如何写一条sql语句来完成上述任务呢，请各位大侠帮忙！

SQL code

insert table AnalyzeJQ
(
       date,
       num,
       amt,

)
select "20120601",
       count(distinct num),
       sum(amt)

from t_log
where (date >= "20120601" and date <= "20120630")

------解决方案--------------------
不是很明白你的意思。
------解决方案--------------------
给个例子，scott用户下emp
select hiredate,count(1),sum(sal) from emp 
where to_char(hiredate,'yyyymmdd') >='19810220' and to_char(hiredate,'yyyymmdd') <='19810222'
group by hiredate
------解决方案--------------------
with t as (
select date'2012-06-01' as fdate,trunc(dbms_random.value(100,1000),2) as price from dual
union all
select date'2012-06-02',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-03',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-04',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-05',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-06',trunc(dbms_random.value(100,1000),2) from dual
)
select fdate,price,sum(price)over(order by fdate) as aa from t
group by fdate,price

FDATE PRICE AA
------------------------- ---------------------- ---------------------- 
2012-06-01 00:00:00 604.63 604.63
2012-06-02 00:00:00 858.61 1463.24
2012-06-03 00:00:00 519.11 1982.35
2012-06-04 00:00:00 878.45 2860.8
2012-06-05 00:00:00 530.97 3391.77
2012-06-06 00:00:00 325.98 3717.75
------解决方案--------------------
用分析函数会比较好，先把数据按日期group by一下计算出每天数据的条数，然后用分析函数算出累计至当天的数据之和。

SQL code

select c_date,
       sum(c_count) over(partition by substr(c_date,1,6) --按月分组
                      order by c_date) as data_count
  from 
  (select c_date,
          count(*) as c_count)
     from tab
    group by c_date)

------解决方案--------------------

SQL code

with t1 as
(
     select date'2012-06-01' c1,'a' c2,100 c3 from dual
     union all
     select date'2012-06-01' c1,'a' c2,200 c3 from dual
     union all
     select date'2012-06-10' c1,'b' c2,300 c3 from dual
     union all
     select date'2012-06-10' c1,'c' c2,400 c3 from dual
     union all
     select date'2012-06-20' c1,'c' c2,500 c3 from dual
     union all
     select date'2012-06-25' c1,'c' c2,600 c3 from dual
     union all
     select date'2012-06-30' c1,'c' c2,700 c3 from dual
     union all
     select date'2012-06-30' c1,'d' c2,700 c3 from dual
)

select c1,
       count(distinct c2) c2,
       (select sum(c3) from t1 b where b.c1 <= a.c1) c3
from t1 a
group by c1


     c1      c2     c3
------------------------------------------
1    2012/6/1    1    300
2    2012/6/10    2    1000
3    2012/6/20    1    1500
4    2012/6/25    1    2100
5    2012/6/30    2    3500