有三个字段，日期（date）和数量（int），学生（varchar），（每个学生有可能有两个日期，也可能只有一个日期）如：
2010-03-01，30，lisa
2011-04-09，50，lisa
我希望实现的是，如果这个学生有两个日期，那么我想用小的日期的数量-10，如果差值>=0,那么小日期的数量等于这个差值，大日期的数量不变。如果差值<0，那么日期小的数量=0，日期大的数量再减去这个差值。如果只有一个日期，就用这个数量-10
谢谢了
------解决方案--------------------
代码迷推荐解决方案：oracle存储过程,http://www.daimami.com/search?q=177537
------解决方案--------------------
SQL> create table tt as select * from
2 (select to_date('2010-03-01','yyyy-mm-dd') tim,25 num,'aa' stu from dual
3 union all select to_date('2011-04-09','yyyy-mm-dd') tim,39 num,'aa' stu from dual
4 union all select to_date('2011-05-15','yyyy-mm-dd') tim,73 num,'bb' stu from dual
5 );
Table created
SQL> select * from tt;
TIM NUM STU
----------- ---------- ---
2010-3-1 25 aa
2011-4-9 39 aa
2011-5-15 73 bb
SQL>
SQL> create or replace procedure p is
2 cursor c is select stu, min(tim) a,max(tim) b,min(num) e,max(num) f from tt group by stu;
3 begin
4 for c1 in c loop
5 if (c1.a = c1.b) then
6 update tt set num = c1.e -10 where stu = c1.stu;
7 elsif (c1.e - 10) >= 0 then
8 update tt set num = c1.e - 10 where tim = c1.a and stu = c1.stu;
9 else
10 update tt set num = 0 where tim = c1.a and stu = c1.stu;
11 update tt set num = c1.f - c1.e + 10 where tim = c1.b and stu = c1.stu;
12 end if;
13 end loop;
14 commit;
15 end;
16 /
Procedure created
SQL> exec p;
PL/SQL procedure successfully completed
SQL> select * from tt;
TIM NUM STU
----------- ---------- ---
2010-3-1 15 aa
2011-4-9 39 aa
2011-5-15 63 bb
SQL> exec p;
PL/SQL procedure successfully completed
SQL> select * from tt;
TIM NUM STU
----------- ---------- ---
2010-3-1 5 aa
2011-4-9 39 aa
2011-5-15 53 bb
SQL> exec p;
PL/SQL procedure successfully completed
SQL> select * from tt;
TIM NUM STU
----------- ---------- ---
2010-3-1 0 aa
2011-4-9 44 aa
2011-5-15 43 bb
------解决方案--------------------
- SQL code
create procedure pro_test()isv_minus number;brgin--更新只有一个日期的学生的数量update tab set 数量=数量-10where 学生 in (select 学生 from tab group by 学生 having count(日期)=1);--更新2个日期的学生并且数量-10>0的记录update tab set 数量=数量-10where (学生,日期) in (select 学生,日期 from (select 学生,数量-10 as new数量,日期 from tab where (学生,日期) in (select 学生,min(日期) from tab group by 学生 having count(日期)=2)) where new数量>0);--更新2个日期的学生并且数量-10<0的记录,日期小的记录为0update tab set 数量=0where (学生,日期) in (select 学生,日期 from (select 学生,数量-10 as new数量,日期 from tab where (学生,日期) in (select 学生,min(日期) from tab group by 学生 having count(日期)=2)) where new数量<0);--取得〈0的学生，数量 建立临时表with temp as select 学生,日期,new数量 from (select 学生,数量-10 as new数量,日期 from tab where (学生,日期) in (select 学生,min(日期) from tab group by 学生 having count(日期)=2)) where new数量<0;--更新2个日期的学生并且数量-10<0的记录,日期大的记录为update tab set 数量=数量-temp.new数量 where (学生,日期) in (select 学生,max(日期) from tab where 学生 in (select 学生 from temp));end pro_test;
------解决方案--------------------
- SQL code
create or replace procedure procisbegin for i in (select rowid,t.datetime,t.numbers, t.sname, sum(1) over(partition by sname) sn, row_number() over(partition by sname order by datetime) rn from t) loop -- if this student has only one record in the table, then update datetime with datetime - 10 if i.sn = 1 then update t set numbers = numbers - 10 where rowid = t.rowid; -- if he/she has more than one record in the table else -- only operate on record whose date is older if i.rn = 1 then if i.numbers - 10 >= 0 then update t set numbers = numbers - 10 where rowid = t.rowid; else update t set numbers = 0 where rowid = t.rowid; update t set numbers = numbers - 10 where sname = i.sname and rowid <> i.rowid; end if; end if; end if; end loop; commit;exception when others then raise;end proc;/