从一系列波动的数据中找到转折点

[drainbow]

数据如下：
id status
1 1
2 1
3 1
4 0
5 0
6 1
7 0
8 0
9 0
10 0
11 1
12 0
13 0
14 0
15 0
16 0

忽略其中连续的点，查询后得出结果：
id status
1 1
3 1
4 0
5 0
6 1
7 0
10 0
11 1
12 0
16 0

用于画出如下的图片

[dianyancao]

建议用你的手把要求的程序帖子移动到 【算法和数据结构】 大家批评一下吧.

[drainbow]

我想SQL直接出来

[--小F--]

是求拐点么??

[AcHerat]

create table tb(id int,status int)
insert into tb
select 1 ,1 union all
select 2 ,1 union all
select 3 ,1 union all
select 4 ,0 union all
select 5 ,0 union all
select 6 ,1 union all
select 7 ,0 union all
select 8 ,0 union all
select 9 ,0 union all
select 10 ,0 union all
select 11 ,1 union all
select 12 ,0 union all
select 13 ,0 union all
select 14 ,0 union all
select 15 ,0 union all
select 16 ,0
go

with cte as
(
	select *,rn = id - (row_number() over (partition by status order by id))
	from tb
)
select *
from cte
where id in (select max(id) from cte group by rn 
			union all 
			select min(id) from cte group by rn)
order by id

drop table tb

/*
id          status      rn
----------- ----------- --------------------
1           1           0
3           1           0
4           0           3
5           0           3
6           1           2
7           0           4
10          0           4
11          1           6
12          0           5
16          0           5

(10 行受影响)

[drainbow]

[Quote=引用 6 楼 acherat 的回复:]

SQL code

create table tb(id int,status int)
insert into tb
select 1 ,1 union all
select 2 ,1 union all
select 3 ,1 union all
select 4 ,0 union all
select 5 ,0 union all
select 6 ,1 union all
select……
[/Quote]

报错，似乎不是通用的函数

服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'with' 附近有语法错误。
服务器: 消息 195，级别 15，状态 1，行 4
'row_number' 不是可以识别的 函数名。

[Linares]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, status int)
insert into #
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 0 union all
select 5, 0 union all
select 6, 1 union all
select 7, 0 union all
select 8, 0 union all
select 9, 0 union all
select 10, 0 union all
select 11, 1 union all
select 12, 0 union all
select 13, 0 union all
select 14, 0 union all
select 15, 0 union all
select 16, 0

select a.* from # a left join # b on a.id=b.id-1 left join # c on a.id=c.id+1
where a.status<>isnull(b.status,~a.status) or a.status<>isnull(c.status,~a.status)

/*
id          status
----------- -----------
1           1
3           1
4           0
5           0
6           1
7           0
10          0
11          1
12          0
16          0
*/

[drainbow]

忽略其中连续的点

[dawugui]

这个,建议用程序去做.