create proc UpdateSign as declare @dtStart datetime declare @dtEnd datetime declare @day int declare @id int declare cur cursor for select id, START_TIME, END_TIME, [day] from tblopen curfetch next from cur into @id, @dtStart, @dtEnd, @day WHILE @@FETCH_STATUS = 0 BEGIN if datediff( day, @dtStart, @dtEnd ) > @day update tbl set [SIGN]='Y' where id=@id else update tbl set [SIGN]='N' where id=@id fetch next from cur into @id, @dtStart, @dtEnd, @day endclose cur deallocate cur GO
好像建立游标会比较好做点吧。 declare @id int declare @theday int declare @chazhi int declare test cursor for select ID,DATEDIFF(day,START_TIME,END_TIME) ,[day] from tb_a open test fetch test into @id, @chazhi,@theday while @@FETCH_STATUS=0 begin if @theday>@chazhi update tb_a set sign='n' where id=@id if @theday<@chazhi update tb_a set sign='y' where id=@id fetch test into @id,@chazhi,@theday end close test DEALLOCATE test 要加分给我哦◎!!!!
create proc UpdateSign
as
declare @dtStart datetime
declare @dtEnd datetime
declare @day int
declare @id int
declare cur cursor for
select id, START_TIME, END_TIME, [day] from tblopen curfetch next from cur into @id, @dtStart, @dtEnd, @day
WHILE @@FETCH_STATUS = 0
BEGIN
if datediff( day, @dtStart, @dtEnd ) > @day
update tbl set [SIGN]='Y' where id=@id
else
update tbl set [SIGN]='N' where id=@id fetch next from cur into @id, @dtStart, @dtEnd, @day
endclose cur
deallocate cur
GO
declare @id int
declare @theday int
declare @chazhi int
declare test cursor for
select ID,DATEDIFF(day,START_TIME,END_TIME) ,[day] from tb_a
open test
fetch test into @id, @chazhi,@theday
while @@FETCH_STATUS=0
begin
if @theday>@chazhi
update tb_a set sign='n' where id=@id
if @theday<@chazhi
update tb_a set sign='y' where id=@id
fetch test into @id,@chazhi,@theday
end
close test
DEALLOCATE test
要加分给我哦◎!!!!