SqlServer 游标逐行更新数据，根据上一行的数据来更新当前行
/*
drop table #tmpTable
create TABLE #tmpTable(
f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
s_id int not null, --供应商id
f_name varchar(255) not null,--水果名称
f_price decimal(8,2) not null --水果价格
);
insert into #tmpTable (s_id,f_name,f_price)
values
(101,'apple',5.8),
(102,'',6.8),
(105,'orange',4.5),
(102,'banana',3.5),
(103,'',8.0),
(104,'grape',7.6),
(101,'',10.5);
select * from #tmpTable
*/
DECLARE cursor_jxsmb CURSOR FOR --定义一个游标
SELECT f_name,f_price FROM #tmpTable; --定于游标的数据源
OPEN cursor_jxsmb --打开游标
DECLARE @LAST NVARCHAR(4000),@THIS NVARCHAR(4000),@price nvarchar(80) --定义两个变量，用来保存上一行和当前行的数据
FETCH NEXT FROM cursor_jxsmb INTO @THIS,@price --设置@this 为当前行的数据
WHILE @@FETCH_STATUS=0 --判断游标是否为读取失败，读取失败则为-1 代表最后一行
BEGIN
IF LEN(@THIS)<>0 --判断当前读取行字段是否为空
BEGIN
SET @LAST=@price --如果不为空则不更新，并且设置@last为当前行的数据
--PRINT @LAST
END
ELSE
BEGIN --如果为空则更新当前行的数据为上一行
UPDATE #tmpTable SET f_name=@LAST WHERE CURRENT OF cursor_jxsmb --更新数据，where 只更新当前游标所在行
--PRINT @LAST
END
FETCH NEXT FROM cursor_jxsmb INTO @THIS,@price --继续读取下一行数据
END
CLOSE cursor_jxsmb --关闭游标
DEALLOCATE cursor_jxsmb --释放游标<br>
select * from #tmpTable
------------------------------------------------
/*
drop table #tmpTable
create TABLE #tmpTable(
f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
s_id int not null, --供应商id
f_name varchar(255) not null,--水果名称
f_price decimal(8,2) not null --水果价格
);
insert into #tmpTable (s_id,f_name,f_price)
values
(101,'apple',5.8),
(102,'',6.8),
(105,'',4.5),
(102,'',3.5),
(103,'',8.0),
(104,'',7.6),
(101,'',10.5);
select * from #tmpTable
*/
DECLARE cursor_jxsmb CURSOR FOR --定义一个游标
SELECT f_name,f_price FROM #tmpTable; --定于游标的数据源
OPEN cursor_jxsmb --打开游标
DECLARE @LAST NVARCHAR(4000),@THIS NVARCHAR(4000),@price nvarchar(80) --定义两个变量，用来保存上一行和当前行的数据
FETCH NEXT FROM cursor_jxsmb INTO @THIS,@price --设置@this 为当前行的数据
WHILE @@FETCH_STATUS=0 --判断游标是否为读取失败，读取失败则为-1 代表最后一行
BEGIN
IF LEN(@THIS)<>0 --判断当前读取行字段是否为空
BEGIN
SET @LAST=@price --如果不为空则不更新，并且设置@last为当前行的数据
--PRINT @LAST
END
ELSE
BEGIN --如果为空则更新当前行的数据为上一行
UPDATE #tmpTable SET f_name=@LAST WHERE CURRENT OF cursor_jxsmb --更新数据，where 只更新当前游标所在行
--PRINT @LAST
SET @LAST=@price
END
FETCH NEXT FROM cursor_jxsmb INTO @THIS,@price --继续读取下一行数据
END
CLOSE cursor_jxsmb --关闭游标
DEALLOCATE cursor_jxsmb --释放游标<br>
select * from #tmpTable
-----------------------------------------------
--此脚本用于从DIM_VESSEL_REPORTING_EVENT表抽取s数据，和表DIM_VESSEL_REPORTING_BERTH_TO_BERTH_LEG数据对比，如果一致说明DIM_VESSEL_REPORTING_BERTH_TO_BERTH_LEG数据抽取逻辑是正确的
drop table #BERTH_TO_BERTH_LEG
create TABLE #BERTH_TO_BERTH_LEG(
imo int ,
customer_id int ,
leg_model varchar(255) ,
leg_start_date datetime,
leg_end_data datetime
);
select * from #BERTH_TO_BERTH_LEG;
declare @IMO int
declare @CUSTOMER_ID int
declare @EVENT varchar(50)
declare @BK_EVENT_DATE datetime
declare @first_date datetime
--declare @leg_start_data datetime
--declare @leg_mode varchar(50)
declare cursor_event cursor scroll
for select BK_VESSEL_IMO,BK_CUSTOMER_ID,EVENT,BK_EVENT_DATE from dbo.DIM_VESSEL_REPORTING_EVENT where BK_VESSEL_IMO = '9336828' and BK_CUSTOMER_ID = '10550971' and SYS_ACTIVE_INDICATOR = 1 order by BK_EVENT_DATE
open cursor_event
while (@@Fetch_Status = 0)
begin
fetch next from cursor_event into @IMO, @CUSTOMER_ID,@EVENT,@BK_EVENT_DATE
if @EVENT='ARRIVAL'
begin
insert into #BERTH_TO_BERTH_LEG (imo,customer_id,leg_model,leg_end_data)values (@IMO, @CUSTOMER_ID,'UnderWay', @BK_EVENT_DATE)
end
else if @EVENT='DEPARTURE'
insert into #BERTH_TO_BERTH_LEG (imo,customer_id,leg_model,leg_end_data)values (@IMO, @CUSTOMER_ID,'InPort', @BK_EVENT_DATE)
end
fetch FIRST from cursor_event into @IMO, @CUSTOMER_ID,@EVENT,@first_date
update top (1) #BERTH_TO_BERTH_LEG set leg_start_date= @first_date
close cursor_event
deallocate cursor_event
--select * from #BERTH_TO_BERTH_LEG
--select BK_VESSEL_IMO,BK_CUSTOMER_ID,EVENT,BK_EVENT_DATE from dbo.DIM_VESSEL_REPORTING_EVENT where BK_VESSEL_IMO = '9336828' and BK_CUSTOMER_ID = '10550971' and SYS_ACTIVE_INDICATOR = 1 order by BK_EVENT_DATE
declare @last datetime,@this datetime,@leg_end_data datetime
declare cursor_get_previous_data cursor
for select leg_start_date,leg_end_data from #BERTH_TO_BERTH_LEG
open cursor_get_previous_data
fetch next from cursor_get_previous_data into @this,@leg_end_data
while (@@FETCH_STATUS=0)
BEGIN
IF LEN(@this)<>0
BEGIN
SET @last=@leg_end_data
END
ELSE
BEGIN
UPDATE #BERTH_TO_BERTH_LEG SET leg_start_date=@last WHERE CURRENT OF cursor_get_previous_data
SET @last=@leg_end_data --设置@leg_end_data为当前行的数据
end
FETCH NEXT FROM cursor_get_previous_data INTO @this,@leg_end_data
END
CLOSE cursor_get_previous_data --关闭游标
DEALLOCATE cursor_get_previous_data --释放游标<br>
--select * from #BERTH_TO_BERTH_LEG
