求一段SQL语句(200分)

  • 主题发起人 主题发起人 delphihsc
  • 开始时间 开始时间
代码:
--建表
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
 
用这个,建一个存储过程
把a代换成你的表名
把wz和da ,fa 分别代换成你的wzbm,JKRQ,JKDJ就可以了

create table #temptable
(wza varchar(10),
maxdate datetime,
maxdj float,
mindate datetime,
mindj float
)

declare @wz varchar(10)

declare my CURSOR FOR
select DISTINCT wz from a

OPEN my

FETCH NEXT FROM my into @wz

while @@FETCH_STATUS=0
begin

insert into #temptable

select aa.wz,aa.da,aa.fa,b.da,b.fa from (select wz,da,fa from a
where wz=@wz
group by wz,da,fa
having
da=(select max(da) from a where wz=@wz)
and fa=(select max(fa) from a where wz=@wz)) aa,
(select wz,da,fa from a
where wz=@wz
group by wz,da,fa
having
da=(select min(da) from a where wz=@wz)
and fa=(select min(fa) from a where wz=@wz)) b
where aa.wz=b.wz


FETCH NEXT FROM my into @wz
end

close my
deallocate my

select * from #temptable

drop table #temptable

 
没必要如此复杂。
就用sql搞定。
select wzbm,sum(mindata) mindata,sum(mindj) mindj,
sum(maxdata) maxdata,sum(maxdj) maxdj from(
(select wzbm,jkrq mindata,jkdj mindj,0 maxdata,0 maxdj from table a,
(select wzbm,min(jkrq) mindata from table group by wzbm) b
where a.wzbm=b.wzbm and a.jkrq=b.mindata)
union all
(select wzbm,0 mindata,0 mindj,jkrq maxdata,jkdj maxdj from table a,
(select wzbm,max(jkrq) maxdata from table group by wzbm) b
where a.wzbm=b.wzbm and a.jkrq=b.maxdata)
) group by wzbm
oracle 运行通过。

呵呵,看了上面的回答,发现自己的方法不是很适用这种情况,有些大材小用。不过想法是好的。
通常只能用存储过程描述的东西也能解决。

 
要是入库的价格不变
可以分开查出需要的数据
然后在结合起来
 
结束吧
我想你的答案早已到手了
 
后退
顶部