帮忙看下这个SQL怎么写
帮忙看下这个SQL如何写
原表:
状态 时间
1 1:00:01
1 1:00:02
2 1:00:03
2 1:00:04
1 1:00:05
1 1:00:06
查询后
状态 时间
1 1:00:01
2 1:00:03
1 1:00:05
请问这个SQL怎么写?
------解决思路----------------------
对于这个数据我的结果是这样 是你期望的结果吗 如果不是 请你说出你期望啥结果。
原表:
状态 时间
1 1:00:01
1 1:00:02
2 1:00:03
2 1:00:04
1 1:00:05
1 1:00:06
查询后
状态 时间
1 1:00:01
2 1:00:03
1 1:00:05
请问这个SQL怎么写?
------解决思路----------------------
with cte as
(select 1 as col1,'1:00:01' as times union all
select 2 as col1,'1:00:03' as times union all
select 2 as col1,'1:00:04' as times union all
select 2 as col1,'1:00:05' as times union all
select 3 as col1,'1:00:06' as times union all
select 1 as col1,'1:00:07' as times union all
select 1 as col1,'1:00:08' as times union all
select 1 as col1,'1:00:09' as times union all
select 1 as col1,'1:00:10' as times union all
select 1 as col1,'1:00:11' as times ),
cte1 as (
select * ,row_number()over(order by times) as n from cte )
select * from cte1 as a
where not exists
(select 2 from cte1 as b where b.n=a.n-1 and a.col1=b.col1)
--结果
col1 times n
----------- ------- --------------------
1 1:00:01 1
2 1:00:03 2
3 1:00:06 5
1 1:00:07 6
(4 行受影响)
对于这个数据我的结果是这样 是你期望的结果吗 如果不是 请你说出你期望啥结果。