select time,sum(aa) from a
where time between to_date(to_char(add_month(sysdate,-1),'yyyymm')||'21') and
to_date(to_char(sysdate,'yyyymm')||'20')
where time between to_date(to_char(add_month(sysdate,-1),'yyyymm')||'21') and
to_date(to_char(sysdate,'yyyymm')||'20')
select time,sum(aa) from a
where time between to_date(to_char(add_month(sysdate,-1),'yyyymm')||'21')) and
to_date(to_char(sysdate,'yyyymm')||'20')
from a
group by Trunc( Add_Months(time - 20,1),'mm');
那立表比较理想做法。
create trigger name_tri
before insert on a
for each row
begin
if to_char(:new.time,'dd')='20' then
gettime;
end if;
end;
/
create procedure gettime
as
begin
insert into b select to_char(sysdate,'yyyy-mm'),sum(aa) from a where time>=(trunc(Add_Months(sysdate,-1),'mm')+21);
end;
/
另外to beckhambobo(beckham):
关于(trunc(Add_Months(sysdate,-1),'mm')+21)的使用,我不太清楚trunc的运算机制是怎么样的?但好像算出来得并不是上月21号(包括)到本月20号(包括)!少了两天!
20号的插入值没算进去
但是改成after以后一直是ORA-04091错误
why?
as
begin
insert into b
select to_char(sysdate,'yyyy-mm'),sum(aa)
from a where to_char(time-20,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm');
end;
/declare
job number;
begin
dbms_job.submit(job,'counttime;',
to_date(to_char(add_months(sysdate,1),'yyyymm')||'210030','yyyymmddhh24mi'),
'to_date(to_char(add_months(sysdate,1),''yyyymm'')||''210030'',''yyyymmddhh24mi'')');
end;
/
每个月21日零点30分定时运行
create trigger name_tri
before insert on a
for each row
begin
if to_char(:new.time,'dd')='21' then
gettime;
end if;
end;
/
把它改成20
create procedure gettime
as
begin
insert into b select to_char(sysdate,'yyyy-mm'),sum(aa) from a where time>=(trunc(Add_Months(sysdate,-1),'mm')+20);
end;
/time建立索引.
qiuyang_wang(小数点) ,beckhambobo(beckham),bird93(大嘴鹦鹉)
均可行,比较起来,似乎qiuyang_wang(小数点) 用试图的方法最简洁
那位高手能分析一下他们在性能上的差别?
select * from a where time between (trunc(sysdate-1)+9/24) and (trunc(sysdate)+8/24);trunc()截取函数,取最近日期.
SQL> select trunc(sysdate) from dual; --以天为截取TRUNC(SYSDATE)
--------------
2003-4-21 00:0
SQL> select trunc(sysdate,'mm') from dual; --以月份为截取,TRUNC(SYSDATE,'MM')
-------------------
2003-4-1 00:00:00当以报表形式要查每个月数据时,那当然用新建一个表作存储,效率上可取.
若是达到这一效果,可用过程序完成。
如:
create trigger name_tri
before insert on a
for each row
begin
if to_char(:new.time,'dd')='21' then
getmonth;
end if;
if to_char(:new.time,'hh')='08' then
getday;
end if;
end;
/
把它改成20
create procedure getmonth
as
begin
insert into b select to_char(sysdate,'yyyy-mm'),sum(aa) from a where time>=(trunc(Add_Months(sysdate,-1),'mm')+20);
end;
/
create procedure getday
as
begin
insert into c select to_char(sysdate-1,'yyyy-mm-dd'),sum(aa) from a where time>=trunc(sysdate-1);
end;
/
getday(:new.time);
end if;create procedure getday(p_date in date)
as
begin
insert into c select to_char(p_date-1,'yyyy-mm-dd'),sum(aa) from a where time>=trunc(p_date-1);
end;
/