with temp as (
select 39 as a, 0.5 as b,'2020-08-25 09:00:00' as c
union all
select 39 as a, 0.4 as b,'2020-08-25 09:01:00' as c
union all
select 39 as a, 0.3 as b,'2020-08-25 09:02:00' as c
union all
select 39 as a, 78 as b,'2020-08-25 09:03:00' as c
union all
select 39 as a, 75 as b,'2020-08-25 09:04:00' as c
union all
select 39 as a, 76 as b,'2020-08-25 09:05:00' as c
union all
select 39 as a, 77 as b,'2020-08-25 09:06:00' as c
union all
select 39 as a, 0.5 as b,'2020-08-25 09:07:00' as c
)
select a,b,d,min(c) as minc,max(c) as maxc from (
select a,b,c,row_number() over (order by c)-row_number() over (partition by b order by c) as d from (
select a,case when b>=20 then '运行' else '停机' end as b,c from temp
) t1
) t2
group by a,b,d
SqlServer求连续分组
最新推荐文章于 2023-12-19 11:19:42 发布