mysql - 在 MySQL 中为每个唯一列选择所有新输入的值

Question

drop table if exists aa;
create temporary table aa(`Country` varchar(100),`Month_Year` varchar(100),
                `created_at` timestamp NOT NULL DEFAULT current_timestamp());
insert into aa(Country,Month_Year,created_at) 
    values('Spain1','JUN2020','2020-09-03 14:20:20'), 
            ('Spain2','JUN2020','2020-09-03 14:20:20'),
            ('Spain3','JUN2020','2020-08-03 14:20:20'),
            ('Spain4','JUN2020','2020-07-03 14:20:20'),
            ('Spain5','JUN2020','2020-06-03 14:20:20'),
            ('Spain6','JUL2020','2020-09-01 14:20:20'),
            ('Spain7','JUL2020','2020-09-03 14:20:20'),
            ('Spain8','JUN2020','2020-09-03 14:20:20'),
            ('Spain9','AUG2020','2020-09-01 14:20:20'),
            ('Spain10','AUG2020','2020-05-03 14:20:20'),
            ('Spain11','AUG2020','2020-05-03 14:20:20'),
            ('Porto1','AUG2019','2020-05-03 14:20:20'),
            ('Porto2','AUG2019','2020-05-03 14:20:20'),
            ('Porto2','AUG2019','2020-05-03 13:20:20'),
            ('Porto2','AUG2011','2020-05-03 13:20:20');
select * from aa;

产生：

如何修改select查询，以便只保留突出显示的列？逻辑是列中的每个唯一值，只留下每个唯一值Month_Year最新的行。created_atMonth_Year

https://www.db-fiddle.com/f/wEuUpiDyEHq8SZtLDpBm5Q/0#

标签： mysqlsqldatetimegreatest-n-per-group

Answer 1

一个带有子查询的选项过滤器：

select t.*
from aa t
where t.created_at = (
    select max(t1.created_at)
    from aa t1
    where t1.month_year = t.month_year
)
order by created_at desc

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f7d29f55ff6f3c573aec0887415b87b5