游标问题 （高分求解）

[ivmyzj]

table
-------------------
id name type
1 a 0
2 a 0
3 a 1
4 a 0
5 b 1
6 b 0
7 b 1
8 b 1

------------
得出结果
id name type
1 a 0
2 a 0
5 b 1

要求用游标运算

[jinjazz]

这是什么规则？

[jinjazz]

直接select

[如一宝宝]

不会帮顶

[ivmyzj]

注:
遇到与a的type不同的值后 删除后面所有a的数据
b也类似

[子陌红尘]

不用游标的处理：

declare @t table(id int,name varchar(10),type int) 
insert into @t select 1,'a',0 
insert into @t select 2,'a',0 
insert into @t select 3,'a',1 
insert into @t select 4,'a',0 
insert into @t select 5,'b',1 
insert into @t select 6,'b',0 
insert into @t select 7,'b',1 
insert into @t select 8,'b',1 

delete t from @t t where exists(select 1 from @t where id<t.id and name=t.name and type!=t.type)

select * from @t
/*
id          name       type        
----------- ---------- ----------- 
1           a          0
2           a          0
5           b          1
*/

[jinjazz]

declare @t table(id int,name varchar(10),type int)
insert into @t select 1,'a',0
insert into @t select 2,'a',0
insert into @t select 3,'a',1
insert into @t select 4,'a',0
insert into @t select 5,'b',1
insert into @t select 6,'b',0
insert into @t select 7,'b',1
insert into @t select 8,'b',1

select * from @t t
where not exists(select 1 from @t where id<t.id and name=t.name and type!=t.type)

[子陌红尘]

如果非得用游标解题：

create table t(id int,name varchar(10),type int) 
insert into t select 1,'a',0 
insert into t select 2,'a',0 
insert into t select 3,'a',1 
insert into t select 4,'a',0 
insert into t select 5,'b',1 
insert into t select 6,'b',0 
insert into t select 7,'b',1 
insert into t select 8,'b',1 
go

declare @id int,@name varchar(10),@name1 varchar(10),@type int,@type1 int,@flag int

set @flag=0

declare cr cursor for 
select id,name,type from t order by id

open cr

fetch next from cr into @id,@name1,@type1
fetch next from cr into @id,@name ,@type

while @@fetch_status=0
begin
    if (@name=@name1)
    begin
        if (@flag=1) or (@type!=@type1)
        begin
            set @flag=1
            delete t where id=@id
        end
    end
    else
    begin
        select @name1=@name,@type1=@type,@flag=0
    end
    
    fetch next from cr into @id,@name ,@type
end
close cr
deallocate cr

select * from t
/*
id          name       type        
----------- ---------- ----------- 
1           a          0
2           a          0
5           b          1
*/
go

drop table t
go