select *
from sap_exc_rate a
where EXC_TYPE in('USD/RMB','JPY/RMB ')
and EFF_DATE=(select max(EFF_DATE) from sap_exc_rate where EXC_RATE=a.EXC_RATE)
from sap_exc_rate a
where EXC_TYPE in('USD/RMB','JPY/RMB ')
and EFF_DATE=(select max(EFF_DATE) from sap_exc_rate where EXC_RATE=a.EXC_RATE)
from sap_exc_rate a
where EXC_TYPE in('USD/RMB','JPY/RMB ')
and EFF_DATE=(select max(EFF_DATE) from sap_exc_rate where EXC_RATE=a.EXC_RATE)
where exe_type in ('USD/RMB','JPY/RMB')
and EFF_DATE = (SELECT MAX(EFF_DATE) from sap_exc_rate)
//上面这么写必需的条件是:最新的两条数据在最新的日期中,而且必须是同一天有两个数据。Select * form sap_exc_rate
where id in (
select top 1 id from sap_exc_rate
where EXC_RATE = 'USD/RMB' order by EFF_DATE desc
)
or id in (
select top 1 id from sap_exc_rate
where EXC_RATE = 'JPY/RMB' order by EFF_DATE desc
)
//这个可以实现不同的条件,即使31日USD为最新,29日JPY为最新同样能够实现
where exe_type in ('USD/RMB','JPY/RMB')
and EFF_DATE = (SELECT MAX(EFF_DATE) from sap_exc_rate)
//上面这么写必需的条件是:最新的两条数据在最新的日期中,而且必须是同一天有两个数据。楼上的 上面可以没有那么写啊 ,你没有加where 吧 看清楚了.... select *
from sap_exc_rate a
where EXC_TYPE in('USD/RMB','JPY/RMB ')
and EFF_DATE=(select max(EFF_DATE) from sap_exc_rate where EXC_RATE=a.EXC_RATE)
from (select max(eff_date) as eff_date,exc_type
from sap_exc_rate
where exc_type in ('USD/RMB','JPY/RMB')
group by exc_type ) a
inner join sap_exc_rate b on a.eff_date=b.eff_date
and a.exc_type = b.exc_type