楼主给的数据例子和要求的不同SQL> select * from test; ID Q
---------- ----------
1 5
2 9
3 10
4 3
5 8
6 7
7 1
8 10
9 9已选择9行。SQL> select a1,a2 q,max(a4) from
2 (select a.id a1,a.q a2,b.id a3,b.q a4 from test a,test b where b.id >= a.id)
3 group by a1,a2; A1 Q MAX(A4)
---------- ---------- ----------
1 5 10
2 9 10
3 10 10
4 3 10
5 8 10
6 7 10
7 1 10
8 10 10
9 9 9已选择9行。已用时间: 00: 00: 00.00
SQL>这是我按你的说法‘依次求002~009,003~009,004~009,005~009,006~009,007~009,008~009 ...........中的最大值’写的,你看看思路吧
---------- ----------
1 5
2 9
3 10
4 3
5 8
6 7
7 1
8 10
9 9已选择9行。SQL> select a1,a2 q,max(a4) from
2 (select a.id a1,a.q a2,b.id a3,b.q a4 from test a,test b where b.id >= a.id)
3 group by a1,a2; A1 Q MAX(A4)
---------- ---------- ----------
1 5 10
2 9 10
3 10 10
4 3 10
5 8 10
6 7 10
7 1 10
8 10 10
9 9 9已选择9行。已用时间: 00: 00: 00.00
SQL>这是我按你的说法‘依次求002~009,003~009,004~009,005~009,006~009,007~009,008~009 ...........中的最大值’写的,你看看思路吧
--经测试符合你的要求
select l.id,l.q,min(r.id),l.q2
from
(
select id,q,(select max(q) as q3 from test_test i where i.id>o.id ) q2
from test_test o
)l
left join test_test r on l.q2=r.q
group by l.id,l.q,l.q2
order by l.id