SQLServer 某列值连续4行相同则输出其1行（测试）

--	drop table test
create table test(name varchar(20),value int)

insert into test(name,value)
values('aa',1),('pp',('hh',-1),('ff',('ee',('aa',('jj',('oo',('uu',('bb',('ll',-1)

select * from test

以上红框中是要输出的结果：输出为1的，连续4行为1则输出第四行值

7 aa 1
13 ff 1
17 ll 1

使用游标的方法:

declare @id int
declare @name varchar(20)
declare @value int
declare @id2 int
declare @value2 int
declare @mk int
set @mk = 1
declare cur cursor local fast_forward 
	for select row_number()over(order by (select 0)) as id,name,value from test
open cur
fetch next from cur into @id,@name,@value
while @@fetch_status = 0
begin
	set @id2 = @id
	set @value2 = @value
	fetch next from cur into @id,@value
	if(@id2+1=@id and @value2 = @value and @value=1)
	begin
		set @mk = @mk +1
		if (@mk = 4)
		begin
			--结果输出或者插入到某个表中
			select @id as id,@name as name,@value as value
			set @mk = 0
		end
	end
	else set @mk = 1
end
close cur
deallocate cur

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!