mysql - 如何获取所有数据并将最新的数据标记为新数据？

我有一个名为Standards的表，其中包括ID，StandardsFamiliy，Standards，Date和Link。
我想显示所有数据，但是我想要同一StandardsFamily中的最新日期，并且Standards标为最新日期。
例如，如果我有

StandardFamily,  Standards, Date
A                   a        2018
A                   a        2017
B                   b        2016
C                   c        2010
C                   c        2011
C                   c        2011
C                   c2       2018

我想检索这个：

StandardFamily,  Standards, Date,  Status
A                   a        2018   New
A                   a        2017   Old
B                   b        2016   New
C                   c        2010   Old
C                   c        2011   Old
C                   c        2012   New
C                   c2       2018   New

我知道如何使用以下查询仅检索最新的查询：

select *
from Standards
where (StandardFamily,Standards,Date)
IN (select StandardFamily,Standards,Max(Date) from Standards group by StandardFamily,Standards)

我想我可能会遵循类似

select
case when (...) then "New" else "Old" end
from Table

任何帮助，将不胜感激！ :)

最佳答案

在MySQL 8+中，您只需使用row_number()：

select s.*,
       (case when row_number() over (partition by StandardFamily, Standards order by date desc) = 1
             then 'New' else 'Old'
        end) as status
from standards s;

在早期版本中，我认为我会使用相关子查询：

select s.*,
       (case when date = (select max(s2.date)
                          from standards s2
                          where s2.StandardFamily = s.StandardFamily and
                                s2.Standards = s.Standards
                          )
              then 'New' else 'Old'
        end) as status
from standards s;

特别是，这可以充分利用(StandardFamily, Standards, Date)上的索引。