下面有两张表,如何取出在当期日期sysdate, date列中的3个月内的qty数据, 并且计算机这三个月的平均数
还请大家帮忙如下,谢谢!
TabaleName:ut1_file No Date
-------------------------------------
001 2007-12-8
002 2006-2-8
003 2010-12-3
004 2010-10-5
005 2008-12-9
006 2009-12-8
... .......TableName:ut2_file No Qty
-------------------------------------------
001 100
002 50
003 30
004 50
005 90
006 300
... .......
还请大家帮忙如下,谢谢!
TabaleName:ut1_file No Date
-------------------------------------
001 2007-12-8
002 2006-2-8
003 2010-12-3
004 2010-10-5
005 2008-12-9
006 2009-12-8
... .......TableName:ut2_file No Qty
-------------------------------------------
001 100
002 50
003 30
004 50
005 90
006 300
... .......
from ut1_file u1 ,ut2_file u2
where u1.date between sysdate and month_add(sysdate,12)
and u1.no=u2.no
select avg(t2.qty)
from ut1_file t1, ut2_file t2
where t1.no=t2.no
and t1.date>=sysdate-interval '3' month;
select avg(t2.qty)
from ut1_file t1, ut2_file t2
where t1.no=t2.no
and t1.date between sysdate-interval '3' month and sysdate;
已写入 file afiedt.buf 1 with ut1_file as(
2 select '001' No,to_date('2007-12-08','yyyy-mm-dd') Date_time from dual union all
3 select '002',to_date('2006-02-08','yyyy-mm-dd') from dual union all
4 select '003', to_date('2010-12-03','yyyy-mm-dd') from dual union all
5 select '004', to_date('2010-10-05','yyyy-mm-dd') from dual union all
6 select '005', to_date('2008-12-09','yyyy-mm-dd') from dual union all
7 select '006',to_date('2009-12-08','yyyy-mm-dd') from dual),
8 ut2_file as(
9 select '001' No,100 Qty from dual union all
10 select '002',50 from dual union all
11 select '003',30 from dual union all
12 select '004',50 from dual union all
13 select '005',90 from dual union all
14 select '006',300 from dual)
15 select t1.date_time,t2.qty,avg(qty) over(order by t1.date_time)
16 from ut1_file t1,ut2_file t2
17 where t1.no=t2.no and t1.date_time between add_months(sysdate,-3) and sysdate
18* order by t1.date_time
scott@YPCOST> /DATE_TIME QTY AVG(QTY)OVER(ORDERBYT1.DATE_TIME)
------------------- ---------- ---------------------------------
2010-10-05 00:00:00 50 50
2010-12-03 00:00:00 30 40
已写入 file afiedt.buf 1 with ut1_file as(
2 select '001' No,to_date('2007-12-08','yyyy-mm-dd') Date_time from dual union all
3 select '002',to_date('2006-02-08','yyyy-mm-dd') from dual union all
4 select '003', to_date('2010-12-03','yyyy-mm-dd') from dual union all
5 select '004', to_date('2010-10-05','yyyy-mm-dd') from dual union all
6 select '005', to_date('2008-12-09','yyyy-mm-dd') from dual union all
7 select '006',to_date('2009-12-08','yyyy-mm-dd') from dual),
8 ut2_file as(
9 select '001' No,100 Qty from dual union all
10 select '002',50 from dual union all
11 select '003',30 from dual union all
12 select '004',50 from dual union all
13 select '005',90 from dual union all
14 select '006',300 from dual)
15 select t1.date_time,t2.qty,avg(qty) over(order by t1.date_time)
16 from ut1_file t1,ut2_file t2
17 where t1.no=t2.no and t1.date_time between add_months(sysdate,-3) and sysdate
18* order by t1.date_time
scott@YPCOST> /DATE_TIME QTY AVG(QTY)OVER(ORDERBYT1.DATE_TIME)
------------------- ---------- ---------------------------------
2010-10-05 00:00:00 50 50
2010-12-03 00:00:00 30 40