create table tb(t int,l decimal(10,1)) insert into tb select 10,0.5 insert into tb select 20,0.6 insert into tb select 30,0.7select id=identity(int,1,1),* into # from tbselect * from # a left join # b on a.id=b.id-1 id t l id t l 1 10 0.5 2 20 0.6 2 20 0.6 3 30 0.7 3 30 0.7 NULL NULL NULL
declare @t int,@l decimal(9,1)declare cur cursor for select t,l from kxsz open cur fetch next from cur into @t,@l while @@fetch_status=0 begin --使用@t,@l做你的事情 fetch next from cur into @t,@l endclose cur deallocate cur
思路:加一个identity列,用循环取
declare @tab table (t varchar(100) , l numeric(10,2)) declare @i int insert into @tab Select '10',0.5 union select '20',0.6 union select '30',0.7select @i = 2 Select identity(int,1,1) id, * into #tb From @tab Select * From #tb Where id <= @i + 1 and id >= @i drop table #tbid t l ----------- ---------------------------------------------------------------------------------------------------- ------------ 2 20 .60 3 30
谢谢 我用另一种方法实现的不过现在有个问题就是:如果我的HLTS>QXTS(max) 就会出错麻烦帮我看看 declare @qxts1 integer,@tsc integer,@qxts2 integer,@jsje decimal(18,2),@kl Numeric(18,3) declare @rq datetime , @fhrq datetime,@mylx Numeric(18,3),@hlts integer,@fhje decimal set @jsje=0 set @qxts2=0 set @kl=0 --set @qjc=O set @tsc=0 --期限天数差 set @fhrq='2008-03-01' set @rq='2008-03-12' set @hlts=datediff(day,@fhrq,@rq) set @fhje=1000 if not exists(select * from kxszh where qxts>@hlts and xsbm='001') return declare C_kxxh cursor for select qxts,mylx from kxszh where xsbm='001' order by qxts open C_kxxh fetch next from C_kxxh into @qxts1,@mylx while @@fetch_status=0 begin set @tsc=@qxts1-@qxts2 select @tsc as tsc ,@qxts1 as qxts1,@qxts2 as qxts2,@mylx as mylx if @hlts>@tsc set @jsje=@jsje+@tsc*@kl*1/(100*30)* @fhje else set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje set @hlts=@hlts-@tsc if @hlts<=0 break set @qxts2=@qxts1 set @kl=@mylx fetch next from C_kxxh into @qxts1,@mylx end if @hlts>0 set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje close C_kxxh deallocate C_kxxhSELECT jsje=@jsje,qxts1=@qxts1,mylx=@mylx,hlts=@hlts,tsc=@tsc
select qxts,mylx from kxszh where xsbm='001' order by qxts
create table kxszh(qxts int,mylx decimal(10,1),xsbm int) insert into tb select 10,0.5,001 insert into tb select 20,0.6,001 insert into tb select 30,0.7,001set @fhrq='2008-03-01' set @rq='2008-03-12' set @hlts=datediff(day,@fhrq,@rq) 如果HLTS>30 就会出错
insert into tb select 10,0.5
insert into tb select 20,0.6
insert into tb select 30,0.7select id=identity(int,1,1),* into # from tbselect * from # a left join # b
on a.id=b.id-1
id t l id t l
1 10 0.5 2 20 0.6
2 20 0.6 3 30 0.7
3 30 0.7 NULL NULL NULL
select t,l from kxsz
open cur
fetch next from cur into @t,@l
while @@fetch_status=0
begin
--使用@t,@l做你的事情
fetch next from cur into @t,@l
endclose cur
deallocate cur
declare @tab table (t varchar(100) , l numeric(10,2))
declare @i int
insert into @tab
Select '10',0.5
union
select '20',0.6
union
select '30',0.7select @i = 2
Select identity(int,1,1) id, * into #tb From @tab
Select * From #tb Where id <= @i + 1 and id >= @i
drop table #tbid t l
----------- ---------------------------------------------------------------------------------------------------- ------------
2 20 .60
3 30
我用另一种方法实现的不过现在有个问题就是:如果我的HLTS>QXTS(max)
就会出错麻烦帮我看看 declare @qxts1 integer,@tsc integer,@qxts2 integer,@jsje decimal(18,2),@kl Numeric(18,3)
declare @rq datetime , @fhrq datetime,@mylx Numeric(18,3),@hlts integer,@fhje decimal
set @jsje=0
set @qxts2=0
set @kl=0
--set @qjc=O
set @tsc=0 --期限天数差
set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
set @fhje=1000
if not exists(select * from kxszh where qxts>@hlts and xsbm='001') return
declare C_kxxh cursor for select qxts,mylx from kxszh where xsbm='001' order by qxts
open C_kxxh
fetch next from C_kxxh into @qxts1,@mylx
while @@fetch_status=0 begin
set @tsc=@qxts1-@qxts2
select @tsc as tsc ,@qxts1 as qxts1,@qxts2 as qxts2,@mylx as mylx
if @hlts>@tsc
set @jsje=@jsje+@tsc*@kl*1/(100*30)* @fhje
else
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
set @hlts=@hlts-@tsc
if @hlts<=0 break
set @qxts2=@qxts1
set @kl=@mylx
fetch next from C_kxxh into @qxts1,@mylx
end
if @hlts>0
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
close C_kxxh
deallocate C_kxxhSELECT jsje=@jsje,qxts1=@qxts1,mylx=@mylx,hlts=@hlts,tsc=@tsc
select qxts,mylx from kxszh where xsbm='001' order by qxts
insert into tb select 10,0.5,001
insert into tb select 20,0.6,001
insert into tb select 30,0.7,001set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
如果HLTS>30
就会出错