with cte1 as --存放每天的开始和结束时间 ( select convert(datetime, '2015-01-01 00:00:00') BTime, convert(datetime, '2015-01-02 00:00:00') ETime union all select convert(datetime, '2015-01-02 00:00:00') BTime, convert(datetime, '2015-01-03 00:00:00') ETime union all select convert(datetime, '2015-01-03 00:00:00') BTime, convert(datetime, '2015-01-04 00:00:00') ETime union all select convert(datetime, '2015-01-04 00:00:00') BTime, convert(datetime, '2015-01-05 00:00:00') ETime union all select convert(datetime, '2015-01-05 00:00:00') BTime, convert(datetime, '2015-01-06 00:00:00') ETime union all select convert(datetime, '2015-01-06 00:00:00') BTime, convert(datetime, '2015-01-07 00:00:00') ETime union all select convert(datetime, '2015-01-07 00:00:00') BTime, convert(datetime, '2015-01-08 00:00:00') ETime union all select convert(datetime, '2015-01-08 00:00:00') BTime, convert(datetime, '2015-01-09 00:00:00') ETime ), cte2 as --存放设备开机和关机时间 ( select convert(datetime, '2015-01-01 08:00:00') BeginTime, convert(datetime, '2015-01-01 12:00:00') EndTime union all select convert(datetime, '2015-01-01 14:00:00') BeginTime, convert(datetime, '2015-01-01 19:00:00') EndTime union all select convert(datetime, '2015-01-02 08:00:00') BeginTime, convert(datetime, '2015-01-02 20:00:00') EndTime union all select convert(datetime, '2015-01-03 08:00:00') BeginTime, convert(datetime, '2015-01-06 12:00:00') EndTime ) select BTime , sum(case when b.BeginTime between a.BTime and a.ETime and b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-b.BeginTime) * 24 when b.BeginTime between a.BTime and a.ETime then convert(float, a.ETime-b.BeginTime) * 24 when b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-a.BTime) * 24 else 24 End) SumTime from cte1 a, cte2 b where a.ETime>b.BeginTime and a.BTime<b.EndTime group by BTime
你也可以LINQ,支持sum,groupby
或者自己循环遍历判断累加
cte1 as --存放每天的开始和结束时间
(
select convert(datetime, '2015-01-01 00:00:00') BTime, convert(datetime, '2015-01-02 00:00:00') ETime union all
select convert(datetime, '2015-01-02 00:00:00') BTime, convert(datetime, '2015-01-03 00:00:00') ETime union all
select convert(datetime, '2015-01-03 00:00:00') BTime, convert(datetime, '2015-01-04 00:00:00') ETime union all
select convert(datetime, '2015-01-04 00:00:00') BTime, convert(datetime, '2015-01-05 00:00:00') ETime union all
select convert(datetime, '2015-01-05 00:00:00') BTime, convert(datetime, '2015-01-06 00:00:00') ETime union all
select convert(datetime, '2015-01-06 00:00:00') BTime, convert(datetime, '2015-01-07 00:00:00') ETime union all
select convert(datetime, '2015-01-07 00:00:00') BTime, convert(datetime, '2015-01-08 00:00:00') ETime union all
select convert(datetime, '2015-01-08 00:00:00') BTime, convert(datetime, '2015-01-09 00:00:00') ETime
),
cte2 as --存放设备开机和关机时间
(
select convert(datetime, '2015-01-01 08:00:00') BeginTime, convert(datetime, '2015-01-01 12:00:00') EndTime union all
select convert(datetime, '2015-01-01 14:00:00') BeginTime, convert(datetime, '2015-01-01 19:00:00') EndTime union all
select convert(datetime, '2015-01-02 08:00:00') BeginTime, convert(datetime, '2015-01-02 20:00:00') EndTime union all
select convert(datetime, '2015-01-03 08:00:00') BeginTime, convert(datetime, '2015-01-06 12:00:00') EndTime
)
select BTime
, sum(case
when b.BeginTime between a.BTime and a.ETime and b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-b.BeginTime) * 24
when b.BeginTime between a.BTime and a.ETime then convert(float, a.ETime-b.BeginTime) * 24
when b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-a.BTime) * 24
else 24
End) SumTime
from cte1 a, cte2 b
where a.ETime>b.BeginTime and a.BTime<b.EndTime
group by BTime
4种情况就是我想要的,
太感谢了大侠,您的sql写的这么溜,有没有什么好的书籍可以推荐啊?
小弟刚刚毕业当程序员,工作效率总是很低