SQL按照顺序时间段统计

最新推荐文章于 2022-09-20 19:05:12 发布

weixin_30687587

最新推荐文章于 2022-09-20 19:05:12 发布

阅读量123

点赞数

原文链接：http://www.cnblogs.com/aaronguo/p/11077756.html

版权

借助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