select
'0:00' 时间点,3 次数 into #t union
select '0:15', 4 union
select '0:30', 1 union
select '0:45', 9 union
select'1:00', 11
select 时间点,
(select sum(次数) from #t t where datediff(mi,t.时间点 ,#t.时间点) between 0 and 30) 半小时内次数
from #t
'0:00' 时间点,3 次数 into #t union
select '0:15', 4 union
select '0:30', 1 union
select '0:45', 9 union
select'1:00', 11
select 时间点,
(select sum(次数) from #t t where datediff(mi,t.时间点 ,#t.时间点) between 0 and 30) 半小时内次数
from #t
declare @tb table(tm varchar(5),cn int)
insert @tb
select '00:00', 3 union all
select '00:15', 4 union all
select '00:30', 1 union all
select '00:45', 9 union all
select '01:00', 11 select * into # from (
select *,(select tm from @tb where 60*(cast(left(tm,2) as int))+cast(right(tm,2) as int)=60*(cast(left(a.tm,2) as int))+cast(right(a.tm,2) as int)+30) as tm2
from @tb as a
) a
where tm2 is not nullselect top 1 * from (
select tm,tm2,cn=(select sum(cn) from @tb where tm>=a.tm and tm<=a.tm2) from # as a
) a
order by cn descdrop table #
/*
tm tm2 cn
----- ----- -----------
00:30 01:00 21
*/
select '0:00' 时间点,3 次数 into #t union
select '0:15', 4 union
select '0:30', 1 union
select '0:45', 9 union
select'1:00', 11select top 1 * from (
select 时段='0:00-0:30',次数=sum(次数) from #t where 时间点 in ('0:00','0:15','0:30')
union all
select '0:30-0:45',sum(次数) from #t where 时间点 in ('0:15','0:30','0:45')
union all
select '0:45-1:00',sum(次数) from #t where 时间点 in ('0:30','0:45','1:00')) a
order by a.次数 descdrop table #t
select '0:15', 114 union
select '0:30', 1 union
select '0:45', 25 union
select '1:00', 11select top 1 * from (
select 时段='0:00-0:30',次数=sum(次数) from #t where 时间点 in ('0:00','0:15','0:30')
union all
select '0:15-0:45',sum(次数) from #t where 时间点 in ('0:15','0:30','0:45')
union all
select '0:30-1:00',sum(次数) from #t where 时间点 in ('0:30','0:45','1:00')) a
order by a.次数 descdrop table #t