求一查询的sql语句

select t.r 行标,t.state 值
from
(
select a.r,a.state,lag(a.state,1) over(order by a.r) last_state,
a.state-lag(a.state,1) over(order by a.r) flag
from (select rownum r,state from test) a--修改test成为你自己的表名
) t
where t.flag<>0 or t.flag is null;
--测试通过
行标 值
---------- ----------
1 0
5 1
8 0

添加1个递增列
select state a,identity(int,1,1) b
into #t1
from table

select top 1 a
from #t1
union all
select b.a
from #t1 a,#t1 b
where
a.b=b.b-1
and
a.a<>b.a

--补充--
我没有oracle环境，不敢保证正确。
这种方法在oracle中不行。我在想一下 想到告诉你啊

select a.* from
(
select a.*,
row_number() over(partition by a.state order by 1) rownumber from table a
) a
where a.rownumber = 1

看看，如果有疑问，先
检索
select a.*,
row_numbe