游标疑难
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
