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 @[email protected]
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 [email protected]
--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