数据:
ID X Y CARRY
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 0
......................
数据结构大概是这样的,在这里面想找出CARRY字段中当由0变为1时CARRY=1的那一行,还有由1变为0时当CARRY=1时的那一行数据
也就是想挑出来打*号的数据,请高手指点
ID X Y CARRY
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 0
......................
数据结构大概是这样的,在这里面想找出CARRY字段中当由0变为1时CARRY=1的那一行,还有由1变为0时当CARRY=1时的那一行数据
也就是想挑出来打*号的数据,请高手指点
create table tb(ID int,X nvarchar(10),Y nvarchar(10),CARRY int)
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
go
;with cte as(
select ROW_NUMBER()over(order by (select 1))rn,* from tb
)select ID,X,Y,CARRY,isnull((select '*' from cte where rn=a.rn-1 and carry!=a.carry),'')flg from cte a
/*
ID X Y CARRY flg
----------- ---------- ---------- ----------- ----
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 0 *
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 0 *
10 .. .. 0
10 .. .. 0 (15 行受影响)
*/
go
drop table tb
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',1
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
insert into tb select 10,'..','..',0
go
;with cte as(
select ROW_NUMBER()over(order by (select 1))rn,* from tb
)select ID,X,Y,CARRY,isnull((select '*' from cte where carry=0 and ((rn=a.rn-1 or rn=a.rn+1) and carry!=a.carry)),'')flg from cte a/*
ID X Y CARRY flg
----------- ---------- ---------- ----------- ----
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 1 *
10 .. .. 1
10 .. .. 1
10 .. .. 1 *
10 .. .. 0
10 .. .. 0
10 .. .. 0 (15 行受影响)*/
go
drop table tb