select min(a2.f_rq-a1.f_rq) (select rownum id1,t.* from (select * from (select * from a order by f_rq) where rownum<=5) t where a.f_wzyy is not null and a.f_wzyy<>0 ) a1, (select rownum-1 id2,t.* from (select * from (select * from a order by f_rq) where rownum<=5) t where a.f_wzyy is not null and a.f_wzyy<>0 ) a2 where a1.id1=a2.id2(+);
不形啊!结果不正确!你这里最后面的where a.f_wzyy is not null and a.f_wzyy<>0 这里面的a.f_wzyy这里的a是哪个表啊!而后面的a.f_wzyy这里的a又代表哪个表啊!!
--select * from a order by f_rq 这里面的a是你实际的表名tb是结果集的别名select count(1) from (select * from (select * from a order by f_rq) where rownum<=5) tb where tb.f_wzyy is not null and tb.f_wzyy<>0 ;
declare cursor day15_cur is select a.f_cdxs,count(a.f_sl) as f_pc from (select * from (select * from dm_dwjd order by f_rq) where rownum<=10) a, dm_wz_his where a.f_wzyy is not null and a.f_wzyy<>0 and a.f_cdxs=dm_wz_his.f_cdxs and a.f_rq<='01-7月-2003' group by a.f_cdxs; day15_rec day15_cur% rowtype; begin for day15_rec in day15_cur loop update dm_wz_his set f_zc_days=day15_rec.f_pc where dm_wz_his.f_cdxs=day15_rec.f_cdxs; end loop; end; 我现在就是要求最近5次中不为零的次数 以上是我要写的游标!!因为还又其它的字段和限制所以还有where语句,我要做一个循环,而且被插入的表dm_wz_his中f_cdxs这个字段是原表dm_dwjd中f_cdxs字段的子集所以需要加一个限制你帮我看看这里这个游标还有什么问题!
select sum(decode(f_wzyy-0,f_wzyy,1,0)),min_value from (select f_wzyy,f_wzyy-lag(f_wzyy,1,99) over(order by f_rq desc) min_value from a order by f_rq desc) where rownum<6;
(select rownum id1,t.* from (select * from (select * from a order by f_rq) where rownum<=5) t where a.f_wzyy is not null and a.f_wzyy<>0 ) a1,
(select rownum-1 id2,t.* from (select * from (select * from a order by f_rq) where rownum<=5) t where a.f_wzyy is not null and a.f_wzyy<>0 ) a2
where a1.id1=a2.id2(+);
rownum 是什么意思能解释一下吗!!
count(1) = count(*) 效率好一点
rownum 是伪列,记录集的行号你先试一下看看行不行,结果有问题的话再说
这里面的a.f_wzyy这里的a是哪个表啊!而后面的a.f_wzyy这里的a又代表哪个表啊!!
where tb.f_wzyy is not null and tb.f_wzyy<>0 ;
select a.f_cdxs,count(a.f_sl) as f_pc from (select * from (select * from dm_dwjd order by f_rq)
where rownum<=10) a, dm_wz_his
where a.f_wzyy is not null and a.f_wzyy<>0
and a.f_cdxs=dm_wz_his.f_cdxs
and a.f_rq<='01-7月-2003'
group by a.f_cdxs;
day15_rec day15_cur% rowtype;
begin
for day15_rec in day15_cur loop
update dm_wz_his set f_zc_days=day15_rec.f_pc
where dm_wz_his.f_cdxs=day15_rec.f_cdxs;
end loop;
end;
我现在就是要求最近5次中不为零的次数
以上是我要写的游标!!因为还又其它的字段和限制所以还有where语句,我要做一个循环,而且被插入的表dm_wz_his中f_cdxs这个字段是原表dm_dwjd中f_cdxs字段的子集所以需要加一个限制你帮我看看这里这个游标还有什么问题!
源表 a:
f_rq("date") f_wzyy("number(10)") f_pc("number(10)")
01-1月-2003 1 1
04-1月-2003 0 0
03-1月-2003 5 7
11-1月-2003 2 8
09-1月-2003 1 4
06-1月-2003 0 0
13-1月-2003 8 1
08-1月-2003 0 0
05-1月-2003 5 3
10-1月-2003 0 0
02-1月-2003 0 0
12-1月-2003 0 0
07-1月-2003 1 1