怎样做一个在数据库中取出本月和上月的数据数据进行比较???

[jackal81]

如题,数据库中的日期形式为,例:20030725.我想做的是比如取出200307的每天的数据与200306的同天的数据进行比较,只把他们列到表里就可以了
例:
| 20030701 | 数据 | 20030601 | 数据 |
| 20030702 | 数据 | 20030602 | 数据 |
| ....... | ... | ... | ... |

...................................

[onejune4450]

select a.thedate,a.qty,b.thedate,b.qty
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)(+)

[onejune4450]

select a.thedate,a.qty,b.thedate,b.qty
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)(+)

[jiezhi]

use function add_months()

[jackal81]

关键是 本月的数据与上月的数据在同一张表里

[jiezhi]

上個月的今天：add_months(sysdte,-1)
下個月的今天：add_months(sysdte,1)

[jiezhi]

关键是 本月的数据与上月的数据在同一张表里
----------->>>
表a(id,myDate,value)
select a.*,b.* from a as now,a as last
where add_months(a.myDate,-1)=b.myDate;

[jiezhi]

sorry:
表a(id,myDate,value)
select a.*,b.* from a as now,a as last
where add_months(now.myDate,-1)=last.myDate;

[onejune4450]

1.因为各月天数不同的原因建议不要用add_months
2.要考虑记录中的天数不一定全的情况。

[beckhambobo]

select max(date_sal),min(date_sal) from
(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

[onejune4450]

表名为aaa，字段：日期为thedate, 值为 qty
每条记录都与对应上月的记录形成一行：

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
/

[onejune4450]

TD TM QTY NTM NQTY
--- ------ ------- ------ -------
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

[jackal81]

jiezhi(浪子) ::请问我的日期是char型的,怎么进行相减?再说要求是上月同天与这月同天比较
20030623::20030523同是23日05,06月比较,怎么办???

[Nickle_Final]

给你这样一个函数，不知道够用不够用：
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;

[Nickle_Final]

select a.*,b.* from a as now,a as last
where my_add_months(a.myDate,-1)=b.myDate(+);