Y
yvtong
Unregistered / Unconfirmed
GUEST, unregistred user!
代码:
--建表
Create Table RuKu (wzbm int ,jkrq datee ,jkdj Money)
Create Table #Tongji (wzbm int , mindate dateTime ,mindj money ,maxdate dateTime , maxdj money)
--测试数据
declare @i int
set @i = 1
while @i<100
begin
insert into ruku (wzbm,jkrq,jkdj) values (rand()*10,getdate()-rand()*10,rand()*100)
set @i=@i+1
end
--存储过程
Create procedure TongjiRuku
as
begin
declare @wzbm int ,@minrq datetime ,@maxrq datetime ,@maxdj money ,@mindj money
declare cs_ruku cursor for
select wzbm,min(jkrq),max(jkrq) from Ruku group by wzbm
open cs_ruku
fetch next from cs_ruku into @wzbm ,@minrq ,@maxrq
while @@fetch_status = 0
begin
Select @wzbm=wzbm ,@maxrq=max(jkrq) ,@maxdj=max(jkdj) from RuKu where wzbm=@wzbm and
DATEDIFF ( day ,jkrq ,@maxrq)=0
group by wzbm,year(jkrq),month(jkrq),day(jkrq)
insert Into #tongji(wzbm,maxdate,maxdj) values (@wzbm,@maxrq,@maxdj)
select @minrq=min(jkrq) ,@mindj=min(jkdj) from RuKu where wzbm=@wzbm and
DATEDIFF ( day ,jkrq ,@minrq)=0
group by wzbm,year(jkrq),month(jkrq),day(jkrq)
update #tongji set minDate = @minrq , minDj = @mindj where wzbm=@wzbm
fetch next from cs_ruku into @wzbm ,@minrq ,@maxrq
end
close cs_ruku
deallocate cs_ruku
end
--执行
exec tongjiruku
--察看
select * from Ruku order by wzbm
select * from #tongji order by wzbm