如题,数据库中的日期形式为,例:20030725.我想做的是比如取出200307的每天的数据与200306的同天的数据进行比较,只把他们列到表里就可以了
例:
| 20030701 | 数据 | 20030601 | 数据 |
| 20030702 | 数据 | 20030602 | 数据 |
| ....... | ... | ... | ... | ...................................
例:
| 20030701 | 数据 | 20030601 | 数据 |
| 20030702 | 数据 | 20030602 | 数据 |
| ....... | ... | ... | ... | ...................................
from tab a,tab b,(select rownum rn from tab where rownum <= 31) c
where a.thedate like '200307%'
and a.thedate like '200306%'
and c.rn = substr(a.thedate,7)(+)
and c.rn = substr(b.thedate,7)(+)
from tab a,tab b,(select rownum rn from tab where rownum <= 31) c
where a.thedate like '200307%'
and b.thedate like '200306%'
and c.rn = substr(a.thedate,7)(+)
and c.rn = substr(b.thedate,7)(+)
下個月的今天:add_months(sysdte,1)
----------->>>
表a(id,myDate,value)
select a.*,b.* from a as now,a as last
where add_months(a.myDate,-1)=b.myDate;
表a(id,myDate,value)
select a.*,b.* from a as now,a as last
where add_months(now.myDate,-1)=last.myDate;
2.要考虑记录中的天数不一定全的情况。
(select col_date,sal,to_char(col_date,'yyyymmdd')||' '||sal date_sal,row_number() over(pratition by trunc(col_date,'mm') order by col_date) rm from tabl where col_date between trunc(add_months(date_value,-1),'mm') and last_day(date_value))
group by rm order by rm
20030623::20030523同是23日05,06月比较,怎么办???
每条记录都与对应上月的记录形成一行: select td,tm,(select qty from aaa where thedate=tm||td) qty,
ntm,(select qty from aaa where thedate=ntm||td) nqty from
(select ltrim(rtrim(to_char(rownum,'00'))) td from aaa where rownum <= 31 ) c,
(select to_char(trunc(to_date(thedate,'yyyymmdd'),'mm'),'yyyymm') tm,
to_char(trunc(add_months(to_date(thedate,'yyyymmdd'),-1),'mm'),'yyyymm') ntm
from aaa
group by trunc(to_date(thedate,'yyyymmdd'),'mm'),
trunc(add_months(to_date(thedate,'yyyymmdd'),-1),'mm')) d
order by tm,td
/
--- ------ ------- ------ -------
14 200302 200301
15 200302 10 200301 10
16 200302 200301
17 200302 200301
18 200302 200301
19 200302 200301
20 200302 200301
21 200302 10 200301 10
22 200302 10 200301 10
23 200302 10 200301 10
24 200302 200301 10
create or replace function my_add_months(p_date varchar2,p_step number) return varchar2 is
v_rtnValue varchar2(20);
begin
v_rtnValue:=substr(p_date,7);
v_rtnValue:=to_char(add_months(to_date(p_date,'yyyymmdd'),p_step),'yyyymm')||v_rtnValue;
return v_rtnValue;
end my_add_months;
where my_add_months(a.myDate,-1)=b.myDate(+);