MYSQL8 按年分组，查询每组总数，并且取得每组最大的id

MySQL8版本现在分组可以和 PostgreSQL 完全一样的写法了，很方便。

select a.*, b.y, b.c from article a join (
    select max(id) id,  y, count(*) c from (
        select row_number() over (partition by FROM_UNIXTIME(ymd, '%Y') order by id desc) rid, id, FROM_UNIXTIME(ymd, '%Y') y, title, thumb from article where cid = 4
    ) article group by y
) b where a.id = b.id