to: bzszp(SongZip) 这样我说具体点,俺的表达能力可能有点问题 获奖情况表: hjqk 主键:内部序号vchar2(20):nbxh 身份代码vchar2(20):sfdm 获奖年度vchar2(4):hjnd (换一组数据也许更能表达出我的想法) 记录如下: sfdm hjnd --------- 001 2003 001 2002 001 2001 001 2000002 2003 002 2002 002 2000003 2003004 2002注明:001是连续从2003-2001都获奖所以统计出来的应该是4 002是2003-2002年连续获奖,2000获奖就不计算在内了,所以统计出来的应该是2 003是只有2003年获奖,统计出来的应该是1 004是2002获奖,2003没有获奖,统计出来的应该是0谢谢, lianhg(lianhg) 能说具体点么? 我的思路是这样的,我现在先写了个游标能统计出一个人的连续获奖情况代码如下,下一步我该怎么做? declare v_sfdm hjqk.sfdm%type; v_rdnd hjqk.rdnd%type; a1 varchar2(1000); i number; tssj hjqk.rdnd%type; cursor testsor is select sfdm, rdnd from hjqk where rdnd<=tssj and sfdm='002' order by sfdm, rdnd desc; begin
tssj:='2003'; open testsor; fetch testsor into v_sfdm,v_rdnd; if testsor%found then begin a1:=v_rdnd; i:=0; while (testsor%found) and (a1=v_rdnd)loop if a1=v_rdnd then i:=i+1; end if; a1:=to_char(to_number(a1)-1); fetch testsor into v_sfdm,v_rdnd; end loop; dbms_output.put_line(to_char(i)); close testsor; end; end if; end;
create function get_num(p_sfdm in varchar2,p_hjnd in varchar2) return number as num number:=0; sums number:=0; curosr t_sor is select hjnd from hjqk where sfdm=p_sfdm order by hjnd; begin for v_sor in t_sor loop p_hjnd:=p_hjnd-num; if p_hjnd=v_sor.hjnd then sums:=sums+1; else exit; end if; num:=num+1; end loop; return sums; end; / select sfdm,get_num(sfdm,to_char(sysdate,'yyyy')) hjcs from hjqk group by sfdm;
考虑使用PROCEDURE
获奖情况表: hjqk
主键:内部序号vchar2(20):nbxh
身份代码vchar2(20):sfdm
获奖年度vchar2(4):hjnd
(换一组数据也许更能表达出我的想法)
记录如下:
sfdm hjnd
---------
001 2003
001 2002
001 2001
001 2000002 2003
002 2002
002 2000003 2003004 2002注明:001是连续从2003-2001都获奖所以统计出来的应该是4
002是2003-2002年连续获奖,2000获奖就不计算在内了,所以统计出来的应该是2
003是只有2003年获奖,统计出来的应该是1
004是2002获奖,2003没有获奖,统计出来的应该是0谢谢, lianhg(lianhg) 能说具体点么?
我的思路是这样的,我现在先写了个游标能统计出一个人的连续获奖情况代码如下,下一步我该怎么做?
declare
v_sfdm hjqk.sfdm%type;
v_rdnd hjqk.rdnd%type;
a1 varchar2(1000);
i number;
tssj hjqk.rdnd%type;
cursor testsor is
select sfdm,
rdnd
from hjqk
where rdnd<=tssj and sfdm='002'
order by sfdm,
rdnd desc;
begin
tssj:='2003';
open testsor;
fetch testsor into v_sfdm,v_rdnd;
if testsor%found then
begin
a1:=v_rdnd;
i:=0;
while (testsor%found) and (a1=v_rdnd)loop
if a1=v_rdnd then i:=i+1; end if;
a1:=to_char(to_number(a1)-1);
fetch testsor into v_sfdm,v_rdnd;
end loop;
dbms_output.put_line(to_char(i));
close testsor;
end;
end if;
end;
return number
as
num number:=0;
sums number:=0;
curosr t_sor is
select hjnd from hjqk where sfdm=p_sfdm order by hjnd;
begin
for v_sor in t_sor loop
p_hjnd:=p_hjnd-num;
if p_hjnd=v_sor.hjnd then
sums:=sums+1;
else
exit;
end if;
num:=num+1;
end loop;
return sums;
end;
/
select sfdm,get_num(sfdm,to_char(sysdate,'yyyy')) hjcs from hjqk group by sfdm;