我的表格显示泵的开/关状态如下
Value timestamp
1 2013-09-01 00:05:41.987
0 2013-09-01 00:05:48.987
1 2013-09-01 00:05:59.987
0 2013-09-01 00:06:15.987
1 2013-09-01 00:06:34.987
etc etc.
我需要一个 MSSQL 查询，该查询可能需要一个月的时间，并告诉我 ON (1) 分钟数和 OFF (0) 分钟数，即占空比
Using CTE and RowNumber() function 小提琴演示 http://sqlfiddle.com/#!3/0a2a8/7:
declare @date date = '20130925'
;with cte as (
select value, timestamp, row_number() over(order by timestamp) rn
from table1
)
select c1.value, sum(datediff(second, c1.timestamp, c2.timestamp)) diffInSeconds
from cte c1 join cte c2 on c1.rn = c2.rn -1
where month(c1.timestamp) = month(@date) and month(c2.timestamp) = month(@date)
group by c1.value
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
