数据库结构如下ID TM(时间) DYP(值)
001 2009-08-07 1
001 2009-08-06 7
001 2009-08-09 10
002 ....................
002 .................
003 ..................现在需要筛选出分组ID后DYP大于5且时间TM离现在最近的一条记录,使用的SQL后的结果如下ID TM(时间) DYP(值)
001 2009-08-09 10
002 ....................
003 ..................
004 .......ORACLE的PL/SQL如何写?谢谢了。
001 2009-08-07 1
001 2009-08-06 7
001 2009-08-09 10
002 ....................
002 .................
003 ..................现在需要筛选出分组ID后DYP大于5且时间TM离现在最近的一条记录,使用的SQL后的结果如下ID TM(时间) DYP(值)
001 2009-08-09 10
002 ....................
003 ..................
004 .......ORACLE的PL/SQL如何写?谢谢了。
select * from(
select tm,sum(dyp)dyp from tt
group by tm
order by tm desc)
where rownum<2
and dyp>5
from (
select id,tm,dyp,row_number()over(partition by id order by tm desc) rn
from a
where dyp>5 )
where rn=1
;
select *
from
(
select id,tm, row_Number() over(partition by dyp order by dyp desc) rn from tt
)
where rn=1 and dyp>5
select *
from
(
select id,tm, row_Number() over(partition by id order by dyp desc) rn from tt
)
where rn=1 and dyp>5