mysql - 按年月选择组内的最后 n 条记录

Question

我有一个表，我需要获取按年()月()ASC 分组的最后 12 条记录。我尝试使用下面的查询，但结果不符合预期。

SELECT * FROM 
(
    SELECT 
        id, 
        tanggal, 
        date_format(tanggal,'%b-%Y') as bulan, 
        sum(sisa_pokok) as jumlah 
    FROM transaksi_detail 
    GROUP BY date_format(tanggal,'%b-%Y') 
    ORDER BY id DESC LIMIT 12
) sub 
ORDER BY id ASC

查询结果如下

我的预期结果是按bulan列顺序按年()，月()排序，如下

Bulan     jumlah
Mar-2018  26600000
Oct-2017  1000000
Sept-2017 4500000
and so on....

Answer 1

EXTRACT(YEAR FROM bulan) as year



SELECT EXTRACT(YEAR FROM tanggal) as year , EXTRACT(MONTH FROM tanggal) as month, id  FROM  table_name  group by year order by month

您可以获得与在分组和排序后获得月份相同的年份，我希望这会对您有所帮助