不知道如下的语句是否满足!
SQL> select * from test;
RQ ID JE
----------- ------ ----------
4/1/2009 AA 10
5/3/2009 AA 20
6/3/2009 AA 20
7/3/2009 AA 20
6/3/2009 BB 20
6/4/2009 BB 100
6/3/2009 BB 50
8/3/2009 BB 20
4/3/2009 CC 10
7/4/2009 CC 10
8/3/2009 CC 30
8/3/2009 CC 20
4/3/2010 CC 20
8/3/2010 CC 10
14 rows selected
SQL>
SQL> select rq,
2 id,
3 je
4 from
5 (select rq,
6 id,
7 je,
8 sum(je) over(partition by id) sum_je
9 from
10 (select rq,
11 id,
12 je,
13 count(*) over(partition by id order by rq range between current row and interval '1' year following) - 1 cnt
14 from test)
15 where cnt > 0)
16 where sum_je > 100;
RQ ID JE
----------- ------ ----------
6/3/2009 BB 50
6/3/2009 BB 20
6/4/2009 BB 100
SQL>