下面这段代码是一段判断下半年出差天数的程序。date1为填写的出发日期，date2为填写的归来日期。date1为必填字段，出差前填写。date2可为NULL，为出差归来后完善。
如下针对date1和date2的各种不同情况，对出差天数进行累加。
可是我发现用游标之后，最后一条记录会重复计算一次
不知道是否如此，还是我的程序另有问题。
select date1,date2 from evection where date1 < '03-1-1' and date1 >= '02-7-1'
or date2 < '03-1-1' and date2 >= '02-7-1' or date2 is null order by date1 asc
declare @date1 datetime,@date2 datetime
declare @sumdays integer
declare @diffdays integer
declare @count integer
set @sumdays = 0
set @count = 0
declare mycursor cursor for
select date1,date2 from evection where date1 < '03-1-1' and date1 >= '02-7-1'
or date2 < '03-1-1' and date2 >= '02-7-1' or date2 is null order by date1 asc
open mycursor
fetch next from mycursor into @date1,@date2
if @date2 is null or @date2 > '03-1-1'
begin
if @date1 >= '02-7-1'
begin
set @diffdays = datediff(dd,@date1,'03-1-1')
set @sumdays = @sumdays + @diffdays
print @diffdays
end
else
begin
set @diffdays = datediff(dd,'02-7-1','03-1-1')
set @sumdays = @sumdays + @diffdays
print @diffdays
end
end
else
if @date1 < '02-7-1'
begin
set @diffdays = datediff(dd,'02-7-1',@date2) + 1
set @sumdays = @sumdays + @diffdays
print @diffdays
end
else
begin
set @diffdays = datediff(dd,@date1,@date2) + 1
set @sumdays = @sumdays + @diffdays
print @diffdays
end
while(@@fetch_status=0)
begin
fetch next from mycursor into @date1,@date2
set @count = @count + 1
if @date2 is null or @date2 > '02-12-31'
begin
if @date1 >= '02-7-1'
begin
set @diffdays = datediff(dd,@date1,'03-1-1')
set @sumdays = @sumdays + @diffdays
print @diffdays
end
else
begin
set @diffdays = datediff(dd,'02-7-1','03-1-1')
set @sumdays = @sumdays + @diffdays
print @diffdays
end
end
else
begin
if @date1 < '02-7-1'
begin
set @diffdays = datediff(dd,'02-7-1',@date2) + 1
set @sumdays = @sumdays + @diffdays
print @diffdays
end
else
begin
set @diffdays = datediff(dd,@date1,@date2) + 1
set @sumdays = @sumdays + @diffdays
print @diffdays
end
end
end
close mycursor
deallocate mycursor
print @sumdays
print @count
---------------------------------------------------------------
select * from test
declare @col1 integer
declare @col2 integer
declare mycursor cursor for
select * from test
open mycursor
fetch next from mycursor into @col1,@col2
while (@@fetch_status=0)
begin
print @col1
fetch next from mycursor into @col1,@col2
end
close mycursor
deallocate mycursor
---------------------------------------------------------------
declare mycursor cursor for
select * from test
open mycursor
fetch next from mycursor into @col1,@col2
while (@@fetch_status=0)
begin
print @col1
fetch next from mycursor into @col1,@col2 //处理完一条后取下一条
end
close mycursor
deallocate mycursor