1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 表结构如下:
GID VaID Mac Acount ActiveTime SubDate
88067 1035395409 90E6BAD84598 1 2010-02-04 14:10:41.560 2010-02-04
86036 3662412139 00306731A792 3 2010-02-04 14:15:45.543 2010-02-04
86599 975963213 00E04CF8AFAF 5 2010-02-04 14:18:47.450 2010-02-04
88625 3658907870 0024210197CC 7 2010-02-04 14:21:47.433 2010-02-04
70025 1902854282 00E04CC7B849 11 2010-02-04 14:22:47.417 2010-02-04
88861 3736328770 001FC6A83210 12 2010-02-04 14:33:47.403 2010-02-04
89985 1035490878 00E04D21914D 1 2010-02-04 14:43:47.357 2010-02-04
88983 3683763045 0011D8C54063 17 2010-02-04 14:44:47.357 2010-02-04
88597 986919413 001A9276C222 22 2010-02-04 14:45:47.357 2010-02-04
76573 1019192692 00E04C04DB03 23 2010-02-04 14:47:47.340 2010-02-04
需要根据GID,VaID,Mac分组,得到一段时间内Acount总数
类似下面的结果:下面是用10分钟分组,这个分组是可以设置的,可能用1小时,或者30分钟等。
GID VaID Mac StartTime EndTime SumAcount
76573 2130706433 0013D3EF3CE1 2009-10-27 09:00:00.000 2009-10-27 09:10:00.000 47
76573 2130706433 0013D3EF3CE2 2009-10-27 09:10:00.000 2009-10-27 09:20:00.000 58
76573 2130706433 0013D3EF3CE3 2009-10-27 09:20:00.000 2009-10-27 09:30:00.000 99
76573 2130706433 0013D3EF3CE9 2009-10-27 09:30:00.000 2009-10-27 09:40:00.000 156
88067 1902854282 001A9276C222 2009-10-27 09:00:00.000 2009-10-27 09:10:00.000 55
88067 986919413 00E04C04DB03 2009-10-27 09:10:00.000 2009-10-27 09:20:00.000 42
88067 1019192692 001A9276C222 2009-10-27 09:20:00.000 2009-10-27 09:30:00.000 85
88067 3736328770 0013D3EF3CE9 2009-10-27 09:30:00.000 2009-10-27 09:40:00.000 12
请各位帮帮忙!
|
得分0 1 2 3 4 5 | sum ( case ... when ... else ... end )
where
时间 between .... and .....
group by
GID,VaID,Mac
|
得分100 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | if object_id( '[tb]' ) is not null drop table [tb]
go
create table [tb]([GID] int ,[VaID] bigint ,[Mac] varchar (12),[Acount] int ,[ActiveTime] datetime,[SubDate] datetime)
insert [tb]
select 88067,1035395409, '90E6BAD84598' ,1, '2010-02-04 14:10:41.560' , '2010-02-04' union all
select 86036,3662412139, '00306731A792' ,3, '2010-02-04 14:15:45.543' , '2010-02-04' union all
select 86599,975963213, '00E04CF8AFAF' ,5, '2010-02-04 14:18:47.450' , '2010-02-04' union all
select 88625,3658907870, '0024210197CC' ,7, '2010-02-04 14:21:47.433' , '2010-02-04' union all
select 70025,1902854282, '00E04CC7B849' ,11, '2010-02-04 14:22:47.417' , '2010-02-04' union all
select 88861,3736328770, '001FC6A83210' ,12, '2010-02-04 14:33:47.403' , '2010-02-04' union all
select 89985,1035490878, '00E04D21914D' ,1, '2010-02-04 14:43:47.357' , '2010-02-04' union all
select 88983,3683763045, '0011D8C54063' ,17, '2010-02-04 14:44:47.357' , '2010-02-04' union all
select 88597,986919413, '001A9276C222' ,22, '2010-02-04 14:45:47.357' , '2010-02-04' union all
select 76573,1019192692, '00E04C04DB03' ,23, '2010-02-04 14:47:47.340' , '2010-02-04'
declare @ begin datetime,@ end datetime,@interval int
select @ begin = '2010-02-04 14:00:00' ,
@ end = '2010-02-04 14:40:00' ,
@interval=10
select
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum (t.[Acount]) as SumAcount
from [tb] t
join
(
select dateadd( minute ,number*@interval,@ begin ) as StartTime,
dateadd( minute ,(number+1)*@interval,@ begin ) as EndTime
from master..spt_values
where type = 'P' and
dateadd( minute ,(number+1)*@interval,@ begin ) <= @ end
) r on t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime
|
不过有点问题,如果增加一些数据 1 2 3 4 | select 88861,3736328770, '001FC6A83210' ,14, '2010-02-04 14:33:47.403' , '2010-02-04' union all
select 88861,3736328770, '001FC6A83210' ,54, '2010-02-04 14:55:47.403' , '2010-02-04' union all
select 88861,3736328770, '001FC6A83210' ,7, '2010-02-04 14:42:47.403' , '2010-02-04' union all
select 88861,3736328770, '001FC6A83210' ,12, '2010-02-04 15:33:47.403','2010-02-04' union all
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | declare @ begin datetime,@ end datetime,@interval int
select @ begin = '2010-02-04 14:00:00' ,
@ end = '2010-02-04 14:50:00' ,
@interval=10
select
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum (t.[Acount]) as SumAcount
from [tb] t
join
(
select
h.[GID],
dateadd( minute ,number*@interval,@ begin ) as StartTime,
dateadd( minute ,(number+1)*@interval,@ begin ) as EndTime
from master..spt_values,( select distinct [GID] from [tb]) h
where type = 'P' and
dateadd( minute ,(number+1)*@interval,@ begin ) <= @ end
) r
on t.[GID]=r.[GID] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime
|
兄弟,依然不对呢 ----------------- 如果加上我刚给的那4条数据,那4条数据,在10分钟为分组时间段的话, 应该有4条结果,但是只有2条! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | declare @ begin datetime,@ end datetime,@interval int
select @ begin = '2010-02-04 14:00:00' ,
@ end = '2010-02-04 14:50:00' ,
@interval=10
select
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum (t.[Acount]) as SumAcount
from [tb] t
join
(
select
h.[GID],h.[VaID],h.[Mac],
dateadd( minute ,number*@interval,@ begin ) as StartTime,
dateadd( minute ,(number+1)*@interval,@ begin ) as EndTime
from master..spt_values,( select distinct [GID],[VaID],[Mac] from [tb]) h
where type = 'P' and
dateadd( minute ,(number+1)*@interval,@ begin ) <= @ end
) r
on t.[GID]=r.[GID] and t.[VaID]=r.[VaID] and t.[Mac]=r.[Mac] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | declare @ begin datetime,@ end datetime,@interval int
select @ begin = '2010-02-04 14:00:00' ,
@ end = '2010-02-04 14:50:00' ,
@interval=10
select
r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime,
isnull ( sum (t.[Acount]),0) as SumAcount
from [tb] t
right join
(
select
h.[GID],h.[VaID],h.[Mac],
dateadd( minute ,number*@interval,@ begin ) as StartTime,
dateadd( minute ,(number+1)*@interval,@ begin ) as EndTime
from master..spt_values,( select distinct [GID],[VaID],[Mac] from [tb]) h
where type = 'P' and
dateadd( minute ,(number+1)*@interval,@ begin ) <= @ end
) r
on t.[GID]=r.[GID] and t.[VaID]=r.[VaID] and t.[Mac]=r.[Mac] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime
|
谢了,可以了。结分!
|