检索每个组中的最后一条记录未获得预期结果 - MySQL

Question

我想从表中获取每个类别的最后两个月的数据。表看起来像：Id 年月类别值1 2019 1 TEST1 102 2018 12 TEST1 10 3 201810 TEST1 104 2018 1 TEST2 105 2018 12 TEST2 106 2018 1 TEST3 10预期输出：Id 年月类别值1 2019 1 TEST1 102 2018 12 TEST1 105 2018 12 TEST2 104 2018 1 TEST2 106 2018 1 TEST3 10我尝试使用：SELECT a.year,a.month,a.value, a.categoryFROM test_data AS aWHERE (SELECT COUNT(*)FROM test_data AS bWHERE b.category = a.category AND (b.year >= a.year AND b.month >= a.month)) <= 2ORDER BY a.year DESC, a.month DESC但它给出了 TEST1 类别的额外记录。我猜是因为它在年度条件下没有按预期工作。请为此提供解决方案

Answer 1

您的首要工作应该是修复您的数据模型并使用date类似 - 的数据类型来存储日期信息，而不是将其分散在不同的列中。这应该像向表中添加新列并从现有列更新它一样简单：

alter table test_data add mydate date;

update test_data set mydate = concat(year, '-', month, '-01');

也就是说，要使您当前的查询正常工作，您需要按如下方式微调日期条件。一种选择使用算术：

SELECT a.year, a.month, a.value, a.category

FROM test_data AS a

WHERE (

    SELECT COUNT(*)

    FROM test_data AS b

    WHERE 

        b.category = a.category 

        AND 100 * b.year + b.month >= 100 * a.year + a.month

) <= 2

ORDER BY a.year DESC, a.month DESC

或者，如果您运行的是 MySQL 8.0，则可以使用row_number()：

SELECT  a.year, a.month, a.value, a.category

FROM (

    SELECT 

        a.*,

        ROW_NUMBER() OVER(PARTITION BY a.category ORDER BY a.year DESC, a.month DESC)

    FROM test_data AS a 

) a

WHERE rn <= 2