日期:2014-05-17 浏览次数:20549 次
create table table1
(KHMC varchar(20), SPDM varchar(10), DJ varchar(10), SL int, XSSL int)
insert into table1
select '广西骆俊峰', '5609B', '100.0000', 12, null union all
select '广西骆俊峰', '5609B', '80.0000', 7, null union all
select '广西骆俊峰', '5609B', '60.0000', 6, null union all
select '广西骆俊峰', '5609B', '50.0000', 13, null union all
select '广西骆俊峰', '5609B', '40.0000', 21, null
create table table2
(khmc varchar(20), spdm varchar(10), sl int, bysl int)
insert into table2
select '广西骆俊峰', '5609B', 20, null
declare @table1_khmc varchar(10)
declare @table1_spdm varchar(20)
declare @table1_sl int
declare @table1_xssl int
--定义table1的游标
declare cur_table1 cursor
for select KHMC,SPDM,sl,xssl from table1 for update --可以游标更新的
declare @table2_khmc varchar(10)
declare @table2_spdm varchar(20)
declare @table2_sl int
--定义table2的游标
declare cur_table2 cursor
for select khmc,spdm,sl from table2 --用于查询的游标
open cur_table2; --打开游标
--从游标中取数,放到变量中
fetch next from cur_table2 into @table2_khmc,@table2_spdm,@table2_sl
while @@FETCH_STATUS = 0 --外层游标cur_table2的遍历
begin
open cur_table1;
fetch next from cur_table1 into @table1_khmc,@table1_spdm,@table1_sl,@table1_xssl
while @@FETCH_STATUS = 0 --内存游标cur_table1的遍历
begin
if (@table1_khmc = @table2_khmc) and (@table2_spdm = @table1_spdm)
begin
update table1
set xssl = case when @table2_sl >= isnull(@table1_sl,0)
then @table1_sl
when @table2_sl < isnull(@table1_sl,0)
&nbs