现有表
create table time
(Time datetime,
Num int
)
insert into time select '2007-11-20 01:02:00',100 union all
select '2007-11-20 01:01:00',100 union all
select '2007-11-20 01:03:00',100 union all
select '2007-11-20 01:07:06',300 union all
select '2007-11-20 01:15:11',900 union all
select '2007-11-20 01:20:47',400 union all
select '2007-11-20 01:31:15',500 union all
select '2007-11-20 01:40:39',300 union all
select '2007-11-20 01:42:10',700 union all
select '2007-11-20 01:57:31',600
表中有如下数据
现要求从00:00:00开始每15分钟为一个单位作为Time-Part字段取出每一个时间段的平均人数\最高人数和最低人数如:
Time-Part avgnum maxnum minnum
1 500 800 100
2 450 500 400
3 750 500 300
4 600 600 600
.
.
.
注意表中的时间可以无限延伸但表中的时间部分依然是15分钟为一个单位
create table time
(Time datetime,
Num int
)
insert into time select '2007-11-20 01:02:00',100 union all
select '2007-11-20 01:01:00',100 union all
select '2007-11-20 01:03:00',100 union all
select '2007-11-20 01:07:06',300 union all
select '2007-11-20 01:15:11',900 union all
select '2007-11-20 01:20:47',400 union all
select '2007-11-20 01:31:15',500 union all
select '2007-11-20 01:40:39',300 union all
select '2007-11-20 01:42:10',700 union all
select '2007-11-20 01:57:31',600
表中有如下数据
现要求从00:00:00开始每15分钟为一个单位作为Time-Part字段取出每一个时间段的平均人数\最高人数和最低人数如:
Time-Part avgnum maxnum minnum
1 500 800 100
2 450 500 400
3 750 500 300
4 600 600 600
.
.
.
注意表中的时间可以无限延伸但表中的时间部分依然是15分钟为一个单位
([Time] datetime,
Num int
)
insert into time select '2007-11-20 01:02:00',100 union all
select '2007-11-20 01:01:00',100 union all
select '2007-11-20 01:03:00',100 union all
select '2007-11-20 01:07:06',300 union all
select '2007-11-20 01:15:11',900 union all
select '2007-11-20 01:20:47',400 union all
select '2007-11-20 01:31:15',500 union all
select '2007-11-20 01:40:39',300 union all
select '2007-11-20 01:42:10',700 union all
select '2007-11-20 01:57:31',600
go
select datediff(mi,'2007-11-20',[Time]) / 15,
avg(num)as avgnum,
max(num) as maxnum,
min(num) as minnum
from [time]
group by datediff(mi,'2007-11-20',[Time]) / 15
/*
Time-Part avgnum maxnum minnum
1 500 800 100
2 450 500 400
3 750 500 300
4 600 600 600
*/drop table [time]
----------- ----------- ----------- -----------
4 150 300 100
5 650 900 400
6 500 700 300
7 600 600 600(所影响的行数为 4 行)
若插入如下数据,并从10:00:00开始统计则必须要在datediff(mi,'2007-11-20 10:00:00',[Time]) / 15中写q全年月日时分秒,若只写10:00:00则提不出正确的数据insert into time select '2007-11-20 10:02:00',100
union all select '2007-11-20 10:14:00',200
union all select '2007-11-20 10:08:00',150
union all select '2007-11-20 10:12:00',550
union all select '2007-11-20 10:34:00',350
union all select '2007-11-20 10:37:00',460
union all select '2007-11-20 10:49:00',60
union all select '2007-11-20 10:44:00',10
union all select '2007-11-20 10:53:00',730
union all select '2007-11-20 10:59:11',750select datediff(mi,'2007-11-20 10:00:00',[Time]) / 15,
avg(num)as avgnum,
max(num) as maxnum,
min(num) as minnum
from [time]
group by datediff(mi,'2007-11-20 10:00:00',[Time]) / 15