34,587
社区成员
发帖
与我相关
我的任务
分享
create table T (date datetime, value int)
insert into T select '2007-1-1 10:00:23',8
insert into T select '2007-1-1 10:01:24',4
insert into T select '2007-1-1 10:05:00',2
insert into T select '2007-1-1 10:06:12',3
insert into T select '2007-1-1 10:08:00',1
insert into T select '2007-1-1 10:12:11',5
declare @min datetime
select @min=convert(datetime,convert(varchar(16),min(date),120)) from T
select dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary
from T
group by dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)
create table # (
时间 datetime,
金额 int
)
insert # select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5
select
[time],
count(1)as 'line',
sum( 金额 )as ' 金额 '
from
(
select
*,
[time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'
else
dateadd(minute,(datediff(minute,'2007-01-01 10:05:00',时间)/5+1) *5 ,'2007-01-01 10:05:00')
end
from #
)a
group by [time]
/*
time line 金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5
(所影响的行数为 3 行)
*/
create table # (
时间 datetime,
金额 int
)
insert # select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5
select time,count(1)as 'line',sum( 金额 )as ' 金额 '
from
(
select *, [time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'
else
dateadd(minute,(abs(datediff(minute,时间,'2007-01-01 10:05:00'))/5+1) *5 ,'2007-01-01 10:05:00')
end
from #
)a
group by [time]
/*
time line 金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5
(所影响的行数为 3 行)
*/
create table tb(时间 datetime , 金额 int)
insert into tb values('2007-1-1 10:00:23' , 8 )
insert into tb values('2007-1-1 10:01:24' , 4 )
insert into tb values('2007-1-1 10:05:00' , 2 )
insert into tb values('2007-1-1 10:06:12' , 3 )
insert into tb values('2007-1-1 10:08:00' , 1 )
insert into tb values('2007-1-1 10:12:11' , 5 )
go
--时间段>=10:00:00 and 时间段<10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 3 14
2007-01-01 10:05:00.000 2 4
2007-01-01 10:10:00.000 1 5
(所影响的行数为 3 行)
*/
--时间段>10:00:00 and 时间段<=10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 2 12
2007-01-01 10:05:00.000 3 6
2007-01-01 10:10:00.000 1 5
(所影响的行数为 3 行)
*/
drop table tb
--环境
declare @t table (
时间 datetime,
金额 int
)
insert @t select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5
select dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112)) as 时间段,
count(*) as 行数,sum(金额) as 总金额
from @t
group by dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112))
--结果
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5
(所影响的行数为 3 行)
create table T (date datetime, value int)
insert into T select '2007-1-1 10:00:23',8
insert into T select '2007-1-1 10:01:24',4
insert into T select '2007-1-1 10:05:00',2
insert into T select '2007-1-1 10:06:12',3
insert into T select '2007-1-1 10:08:00',1
insert into T select '2007-1-1 10:12:11',5
declare @min datetime
select @min=convert(datetime,convert(varchar(16),min(date),120)) from T
select dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary
from T
group by dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)
/*
date cout sumary
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5
*/
drop table T
--先给个按小时的分组.
比如有一个表timeTable,有字段id,score,inputDate.
数据如下
id score inputDate
1 2 '2007-4-5 3:33:33'
2 1 '2007-4-5 4:33:33'
3 4 '2007-3-5 3:33:33'
4 2 '2007-4-2 2:33:33'
我要按时间分组统计score,结果如下
0:00-1:00 0
1:00-2:00 0
2:00-3:00 2
3:00-4:00 6
4:00-5:00 1
5:00-6:00 0
....
23:00-24:00 0
declare @a table(id int, score int, inputDate smalldatetime)
insert @a select 1, 2, '2007-4-5 3:33:33'
union all select 2, 1, '2007-4-5 4:33:33'
union all select 3, 4, '2007-3-5 3:33:33'
union all select 4, 2, '2007-4-2 2:33:33'
select right(100+a,2)+':00-'+right(100+b,2)+':00',
sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end)
from @a a ,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'
--------------- -----------
00:00-01:00 0
01:00-02:00 0
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
05:00-06:00 0
06:00-07:00 0
07:00-08:00 0
08:00-09:00 0
09:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0
17:00-18:00 0
18:00-19:00 0
19:00-20:00 0
20:00-21:00 0
21:00-22:00 0
22:00-23:00 0
23:00-24:00 0
(所影响的行数为 24 行)
--环境
declare @t table (
时间 datetime,
金额 int
)
insert @t select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5
select dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112)) as 时间段,
count(*) as 行数,sum(金额) as 总金额
from @t
group by dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112))
--结果
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 2 12
2007-01-01 10:10:00.000 3 6
2007-01-01 10:15:00.000 1 5
(所影响的行数为 3 行)