--按date排序 with TT as (select first_value(id) over (order by date range current row and 499 following) first_id, first_value(date) over (order by date range current row and 499 following) first_date, last_value(id) over (order by date range current row and 499 following) last_id, last_value(date) over (order by date range current row and 499 following) last_date, sum(point) over (order by date range current row and 499 following) sum_point from match) select * from TT where sum_point=(select max(sum_point) from TT);
参考下面的语句,按那列排序,就在table2中order by那列。 with table1 as ( select ABS(MOD(DBMS_RANDOM.RANDOM,100)) id from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual ) , table2 as ( select rownum rn, id from table1 --生成序号 ) , table3 as ( select rn, id, (select sum(id) id_sum from table2 b where b.rn>=a.rn and b.rn<a.rn+5) id_sum from table2 a --计算当前记录及后续4条(共5条)记录的合计 ) select rn,id,id_sum,(select id from table3 where rn=a.rn+4) id_max from table3 a where id_sum = (select max(id_sum) from table3) --查出合计最大的那一条,为起始,再加上4,就是截止
--按date排序
with TT as (select first_value(id) over (order by date range current row and 499 following) first_id,
first_value(date) over (order by date range current row and 499 following) first_date,
last_value(id) over (order by date range current row and 499 following) last_id,
last_value(date) over (order by date range current row and 499 following) last_date,
sum(point) over (order by date range current row and 499 following) sum_point from match)
select * from TT where sum_point=(select max(sum_point) from TT);
with table1 as
(
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) id from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual union all
select ABS(MOD(DBMS_RANDOM.RANDOM,100)) from dual
)
, table2 as
(
select rownum rn, id from table1 --生成序号
)
, table3 as
(
select rn, id, (select sum(id) id_sum from table2 b where b.rn>=a.rn and b.rn<a.rn+5) id_sum from table2 a --计算当前记录及后续4条(共5条)记录的合计
)
select rn,id,id_sum,(select id from table3 where rn=a.rn+4) id_max from table3 a where id_sum = (select max(id_sum) from table3) --查出合计最大的那一条,为起始,再加上4,就是截止