关于统计一段时间内数据的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