SQL按照顺序时间段统计
借助master..spt_values表
按照时间(半小时)划分统计时间段:
select cast(DATEADD(MINUTE,B.number*30,dateInfo.dday) as time) StartTime, cast(DATEADD(SECOND,-1,DATEADD(MINUTE,(B.number+1)*30,dateInfo.dday)) as time) EndTime from (select top 1 convert(varchar(10),'07:00',120) dday) dateInfo cross join master..spt_values b where b.type = 'P' and number <24
统计时间段(半小时)范围内总数量:
with cte as ( select cast(DATEADD(MINUTE,B.number*30,dateInfo.dday) as time) StartTime, cast(DATEADD(SECOND,-1,DATEADD(MINUTE,(B.number+1)*30,dateInfo.dday)) as time) EndTime from (select top 1 convert(varchar(10),'07:00',120) dday) dateInfo cross join master..spt_values b where b.type = 'P' and number <24 ) select StartTime, EndTime, sum(PreSum) PreSum, sum(PreUsed) PreUsed from ( select StartTime,EndTime,case when hbStartTime is null then 0 else 1 end PreSum,case when Used ='1' then 0 else 1 end PreUsed from cte dateInfo left join yshb_timetable b on cast(b.hbStartTime as time) between StartTime and EndTime where b.YshbID='查询条件' )a group by StartTime,EndTime