我在统计一个表的数据时,查询出来的结果是这样的:
count date
1 2010-03-08
1 2010-03-09
1 2010-03-10
20 2010-03-14
8 2010-03-15
7 2010-03-16
4 2010-03-17
3 2010-03-18
2 2010-03-19
9 2010-03-20
3 2010-03-21
3 2010-03-24
1 2010-03-25
1 2010-03-29
1 2010-04-02现在的统计周期都是按天算的,如果想自定义统计周期的话该怎么搞?
例如把3天作为一个周期,那么数据就是这样的count period
3 1
35 2
9 3
………………实在是想不出来啊,所以请各位高手给指点下,谢谢了。
count date
1 2010-03-08
1 2010-03-09
1 2010-03-10
20 2010-03-14
8 2010-03-15
7 2010-03-16
4 2010-03-17
3 2010-03-18
2 2010-03-19
9 2010-03-20
3 2010-03-21
3 2010-03-24
1 2010-03-25
1 2010-03-29
1 2010-04-02现在的统计周期都是按天算的,如果想自定义统计周期的话该怎么搞?
例如把3天作为一个周期,那么数据就是这样的count period
3 1
35 2
9 3
………………实在是想不出来啊,所以请各位高手给指点下,谢谢了。
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([count] int,[date] datetime)
insert [tb]
select 1,'2010-03-08' union all
select 1,'2010-03-09' union all
select 1,'2010-03-10' union all
select 20,'2010-03-14' union all
select 8,'2010-03-15' union all
select 7,'2010-03-16' union all
select 4,'2010-03-17' union all
select 3,'2010-03-18' union all
select 2,'2010-03-19' union all
select 9,'2010-03-20' union all
select 3,'2010-03-21' union all
select 3,'2010-03-24' union all
select 1,'2010-03-25' union all
select 1,'2010-03-29' union all
select 1,'2010-04-02'
---查询---
select
sum([count]) as [count],
(rn-1)/3+1 as period
from
(select rn=row_number() over(order by [date]),* from tb)t
group by
(rn-1)/3+1---结果---
count period
----------- --------------------
3 1
35 2
9 3
15 4
3 5(5 行受影响)
drop table tb
Go
Create table tb([count] int,[date] Datetime)
Insert tb
select 1,'2010-03-08' union all
select 1,'2010-03-09' union all
select 1,'2010-03-10' union all
select 20,'2010-03-14' union all
select 8,'2010-03-15' union all
select 7,'2010-03-16' union all
select 4,'2010-03-17' union all
select 3,'2010-03-18' union all
select 2,'2010-03-19' union all
select 9,'2010-03-20' union all
select 3,'2010-03-21' union all
select 3,'2010-03-24' union all
select 1,'2010-03-25' union all
select 1,'2010-03-29' union all
select 1,'2010-04-02'
Go
if object_id('tempdb..#')is not null drop table #
select id=identity(int,1,1),* into # from tb
select sum([count])
from(
select (ID-1)/3 px,
[count]
from #)t
group by px
/*
-----------
3
35
9
15
3
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([count] int,[date] datetime)
insert [tb]
select 1,'2010-03-08' union all
select 1,'2010-03-09' union all
select 1,'2010-03-10' union all
select 20,'2010-03-14' union all
select 8,'2010-03-15' union all
select 7,'2010-03-16' union all
select 4,'2010-03-17' union all
select 3,'2010-03-18' union all
select 2,'2010-03-19' union all
select 9,'2010-03-20' union all
select 3,'2010-03-21' union all
select 3,'2010-03-24' union all
select 1,'2010-03-25' union all
select 1,'2010-03-29' union all
select 1,'2010-04-02'--2000
select sum([count]) as [count],(iRow-1)/3+1 as period from
(
select (select count(*) from [tb] e2
where e1.[date] >= e2.[date]) as iRow,e1.*
from [tb] e1
) as b
group by
(iRow-1)/3+1
/*(15 行受影响)
count period
----------- -----------
3 1
35 2
9 3
15 4
3 5(5 行受影响)*/
sqlserver 2005
select w.id/4 period, min(f2) date_min, max(f2) date_max, sum(c) date_sum
from
(select row_number() over(order by f2 asc)id, f2, count(f2) c
from t1
group by f2
)w
group by w.id/4说明sql语句中的4是所说有统计周期3+1测试数据
create table t1
(f1 int identity(1, 1),
f2 smalldatetime
)insert into t1(f2) values('2010-01-03');
insert into t1(f2) values('2010-01-04');
insert into t1(f2) values('2010-01-05');
insert into t1(f2) values('2010-01-06');
insert into t1(f2) values('2010-01-09');
insert into t1(f2) values('2010-01-12');
insert into t1(f2) values('2010-01-13');
insert into t1(f2) values('2010-01-15');
insert into t1(f2) values('2010-01-16');
insert into t1(f2) values('2010-01-20');
通过反复执行上面的insert into 语句,可插入不同日期的多条记录。