看这样行不行:
SELECT sum(money) FROM tab WHERE flag=1 and MONTHS_BETWEEN(curdate, sysdate)<=intervial group by name;
不过,我没试过months_between的右值是表中字段的情形。
SELECT sum(money) FROM tab WHERE flag=1 and MONTHS_BETWEEN(curdate, sysdate)<=intervial group by name;
不过,我没试过months_between的右值是表中字段的情形。
解决方案 »
- win7下怎么利用后台进程MMAN进行SGA动态管理
- 怎么删除.dbf文件?
- Oracle数据库连接问题,求高手解决啊
- 求一个sql语法比对表格
- ORACLE-SQLCODE=-942是什么问题
- oracle中能操纵ftp命令吗?
- 怎么能查看数据库最近有哪些用户登录过?
- 求助各位老大(很有挑战性的问题)
- 我和同事合写的<SQL Studio数据库集成管理工具>,使程序员免受SQL Plus之苦,需要的发E-mail给我 [email protected]
- 高分相送,如何提高查寻效率(在线等待)
- 有关Oracle中簇与索引关系的问题!
- D:\oracle\ora92\database\archive\目录下出现了很多ARC00250.001之类的文件而且基本是一天一个
select name,sum(money) from tab a where curdate between (select cur_date from tab
where flag =1 and name = a.name) and (select add_months(cur_date,intervial) from tab
where flag =1 and name = a.name) group by name还是建议你写程序或存储过程,函数之类的
请问如何来写存储过程呢?请赐教!
先建表my_tab:
create table my_tab (name varchar2(50),money number(10));然后执行以下脚本declare
m_interval number(6);
m_name varchar2(50);
m_curdate date;
m_money number(10);
type curtype is ref cursor;
m_cur curtype;
sql varchar2(200);begin
sql := 'select name,interval,curdate from tab where flag =1';
open m_cur for sql;
loop
fetch m_cur into m_name,m_interval,m_curdate;
exit when m_cur%notfound;select nvl(sum(money),0) into m_money from tab where name = m_name and curdate between m_curdate and add_months(m_curdate,m_interval);insert into my_tab values(m_name,m_money);end loop;
close m_cur;commit;end;
/
select sum(money) from tab where name=t1.name and curdate>t1.curdate and curdate<=add_months(t1.curdate,t1.intervial)),0)
from tab t1
where flag=1;