请教一个SQL语句

[任等平]

下面有两张表,如何取出在当期日期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
... .......

[iqlife]

select avg(u2.qty)
from ut1_file u1 ,ut2_file u2
where u1.date between sysdate and  month_add(sysdate,12)
and u1.no=u2.no

[xman_78tom]

select avg(t2.qty)
from ut1_file t1, ut2_file t2
where t1.no=t2.no
and t1.date>=sysdate-interval '3' month;

[xman_78tom]

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

[爱的世界999]

楼主麻烦出具体解决方案！谢谢啊

[任等平]

感谢大家,昨天想了好久,一直感觉脑子里有影响,就想不出来,晚上睡了一觉,早上过来问题就解决了,
使用变量就可以解决了,