关于统计一段时间内数据的SQL话语

关于统计一段时间内数据的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_logwhere (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) c3from t1 agroup by c1     c1      c2     c3------------------------------------------1    2012/6/1    1    3002    2012/6/10    2    10003    2012/6/20    1    15004    2012/6/25    1    21005    2012/6/30    2    3500 
  

 

查看更多 下一篇