sql语句为 select distinct tr.id,
s.frate,
s.dteffective
from sett_transopenfixeddeposit tr,
sett_interestrate swhere tr.nnoticeday = s.nnotifytypeid
查询出的结果为: ID FRATE DTEFFECTIVE
6439 1.62 2004-1-1
6439 1.88 2011-5-25
6441 1.08 2004-1-1
6441 3.2 2011-1-1 这里ID有重复的行,我需要取出列DTEFFECTIVE 距离今天最近的那条数据
就是期望的结果应该是 ID FRATE DTEFFECTIVE
6439 1.88 2011-5-25
6441 3.2 2011-1-1请问sql语句该如何修改。
s.frate,
s.dteffective
from sett_transopenfixeddeposit tr,
sett_interestrate swhere tr.nnoticeday = s.nnotifytypeid
查询出的结果为: ID FRATE DTEFFECTIVE
6439 1.62 2004-1-1
6439 1.88 2011-5-25
6441 1.08 2004-1-1
6441 3.2 2011-1-1 这里ID有重复的行,我需要取出列DTEFFECTIVE 距离今天最近的那条数据
就是期望的结果应该是 ID FRATE DTEFFECTIVE
6439 1.88 2011-5-25
6441 3.2 2011-1-1请问sql语句该如何修改。
( select *
from sett_interestrate s
where s.DTEFFECTIVE =
(select max(DTEFFECTIVE)
from sett_interestrate d
where s.nnotifytypeid = d.nnotifytypeid) s
where tr.nnoticeday = s.nnotifytypeid
s.frate,
s.dteffective
from sett_transopenfixeddeposit tr,
sett_interestrate s
where tr.nnoticeday = s.nnotifytypeid and s.dteffective in (
select max(s.dteffective) from sett_transopenfixeddeposit tr,
sett_interestrate s
where tr.nnoticeday = s.nnotifytypeid);
ID FRATE DTEFFECTIVE
6439 1.88 2011-5-25
--分析函数很简单的
select id,frate,dteffective from (
select id,frate,dteffective,
row_number()over(partition by id order by dteffective desc) rn
from
(select distinct tr.id,s.frate,s.dteffective
from sett_transopenfixeddeposit tr,sett_interestrate s
where tr.nnoticeday = s.nnotifytypeid )
)
where rn=1;
row_number()over(partition 这几个是什么···