游标疑难
create table A(col smallint,ds smallint)
insert into A
select 1,null
union all
select 1,null
union all
select 2,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 0,null
union all
select 0,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 0,null
union all
select 1,null
declare @后 smallint
declare @前 smallint
declare cus scroll cursor
for
select col from A
open cus
fetch last from cus into @前
while @@fetch_status=0
begin
--/**
if @后 is null
begin
set @后=@前
end
--**/
--print @前
--print @后
update A
set ds=case
when (@前=3) and (@后=0) then 4
when (@前=3) and (@后=1) then 3
when (@前=2) and (@后=3) then 0
when (@前=2) and (@后=1) then 2
when (@前=1) and (@后=2) then 0
when (@前=1) and (@后=1) then 1
when (@前=0) and (@后=0) then 0
when (@前=0) and (@后=1) then 0 else 0 end
where col=@前
--print @col
fetch relative -1 from cus into @前
end
close cus
deallocate cus
select * from A
--------------
如何修改代码，使结果显示为：
col ds
1 1
1 0
2 2
1 0
2 0
3 3
1 0
2 0
3 4
0 0
0 0
1 0
2 0
3 4
0 0
1 1


[解决办法]
原来并非无解

create table A(col smallint,ds smallint)
insert into A
select 1,null
union all
select 1,null
union all
select 2,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 0,null
union all
select 0,null
union all
select 1,null
union all
select 2,null
union all
select 3,null
union all
select 0,null
union all
select 1,null

declare @t table(id int IDENTITY(1,1),col smallint,ds smallint)

insert @t
select * from a


declare @后 smallint
declare @前 smallint
declare @Id前 int

declare cus scroll cursor
for

select col,id from @t
open cus

fetch last from cus into @前,@Id前

while @@fetch_status=0
begin
--/**
if @后 is null
begin
set @后=@前
end
--**/
print @前
print @后

update @t
set ds=case
when (@前=3) and (@后=0) then 4
when (@前=3) and (@后=1) then 3
when (@前=2) and (@后=3) then 0
when (@前=2) and (@后=1) then 2
when (@前=1) and (@后=2) then 0
when (@前=1) and (@后=1) then 1
when (@前=0) and (@后=0) then 0
when (@前=0) and (@后=1) then 0 else 0 end
where id=@id前
--print @col
set @后=@前

fetch relative -1 from cus into @前,@id前
end
close cus
deallocate cus

truncate table a
insert a
select col,ds from @t
select * from A