如何使用日期列过滤数据？

How to fetch data from 3 tables with max date

如何从具有最大日期的3个表中获取数据

select 
    c.model_key, v.id, v.category_Id, v.Text1, v.Numeric1,
    c.Name, fx.Currency_code, fx.Rate, fx.effective_dt 
from 
    aps.v_m_fxrates fx 
join 
    aps.t_m_value v on fx.Currency_code = v.Text1 
join 
    aps.t_m_category c on v.Category_Id = c.Id 
where 
    c.Name = 'FXRates' 
    and c.Model_Key = 25 
    and v.Text1 = fx.Currency_code 
    and fx.version = 2

Using the above query I get results, but the results appearing for all the effective_dt. Instead of this, I need to pick only the records with latest effective_dt. In the below image, there is 2 records with AED in which the one with latest date is 1999-03-31 which must be returned. After this I do not know how to proceed and filter the result further to achieve the output.

使用上面的查询我得到结果,但结果显示为所有effective_dt。而不是这个,我需要只选择最新的effective_dt记录。在下图中,有2条AED记录,其中最新日期为1999-03-31,必须返回。在此之后我不知道如何继续并进一步过滤结果以实现输出。

I also tried this

我也尝试过这个

select 
    c.model_key, v.id, v.category_Id, v.Numeric1,
    c.Name, fx.Currency_code, fx.Rate, fx.effective_dt 
from 
    aps.v_m_fxrates fx 
join 
    aps.t_m_value v on fx.Currency_code = v.Text1 
join 
    aps.t_m_category c on v.Category_Id = c.Id 
where 
    c.Name = 'FXRates' 
    and c.Model_Key = 25 
    and v.Text1 = fx.Currency_code 
    and fx.version = 2 
    and fx.effective_dt in (select MAX(effective_dt) 
                            from aps.v_m_fxrates)

but nothing is being returned.

但没有任何回报。

Actual output:

Expected output:

3 个解决方案

#1

Use the row_number() function in a subquery like this:

在子查询中使用row_number()函数,如下所示:

select 
     c.model_key, v.id, v.category_Id, v.Text1, v.Numeric1,
     c.Name, fx.Currency_code, fx.Rate, fx.effective_dt 
from (
    select
         Currency_code,Rate,effective_dt
        ,SeqNo = row_number() over (partition by Currency_code order by effective_dt desc)
    from aps.v_m_fxrates 
    where version = 2
) fx 
join 
    aps.t_m_value v on fx.Currency_code = v.Text1 
join 
    aps.t_m_category c on v.Category_Id = c.Id 
where c.Name = 'FXRates' 
  and c.Model_Key = 25 
  and v.Text1 = fx.Currency_code 
  and fx.SeqNo = 1

#2

Try use operator TOP 1 and corresponding ORDER BY

尝试使用运算符TOP 1和相应的ORDER BY

For example:

SELECT TOP 1 C.model_key,
             V.id,
             ...
FROM ...
ORDER BY FX.effective_dt DESC

Another option that you have is using aggregate function and correlation in where clause.

您拥有的另一个选项是在where子句中使用聚合函数和相关性。

select C.model_key,
       V.id,
       V.category_Id, 
       V.Text1,
       V.Numeric1,
       C.Name, 
       FX.Currency_code,
       FX.Rate,
       FX.effective_dt 
FROM aps.v_m_fxrates AS FX
    JOIN aps.t_m_value AS V on FX.Currency_code = V.Text1 
    JOIN aps.t_m_category AS C on V.Category_Id = C.Id 
WHERE C.Name = 'FXRates' 
          AND C.Model_Key = 25
          AND FX.version = 2
          AND FX.effective_dt = 
                (SELECT MAX(effective_dt) FROM aps.v_m_fxrates AS FX2
                 WHERE FX2.currency_code = FX.currency_code)

#3

select 
    c.model_key, v.id,v.category_Id, v.Numeric1,
    c.Name, fx.Currency_code, fx.Rate, fx.effective_dt 
from 
    aps.v_m_fxrates fx 
join 
    aps.t_m_value v on fx.Currency_code = v.Text1 
join 
   aps.t_m_category c on v.Category_Id = c.Id 
where 
   c.Name = 'FXRates' 
   and c.Model_Key = 25 
   and v.Text1 = fx.Currency_code 
   and fx.version = 2 
   and fx.effective_dt = (select MAX(effective_dt) from aps.v_m_fxrates)

Try this; you used fx.effective_dt in, but instead you have to use = because you want maximum date record. After using = it will return only one row.

试试这个;你使用了fx.effective_dt,但你必须使用=因为你想要最大日期记录。使用=后,它只返回一行。

#1