在MySQL中获取每个月的最后记录… - Get last record of each month in MySQL....?

Get last record of each month in MySQL....?

本文关键字：最后记录 MySQL 获取 | 更新日期: 2023-09-26

我在为MySQL编写查询时有一个问题。我在DB

中有以下字段

id     created_on            status
1      2011-02-15 12:47:09    1 
2      2011-02-24 12:47:09    1
3      2011-02-29 12:47:09    1
4      2011-03-11 12:47:09    1
5      2011-03-15 12:47:09    1
6      2011-03-22 12:47:09    1
7      2011-04-10 12:47:09    1
8      2011-04-11 12:47:09    1

我需要select the last record of each month。这是month FEB record # 3, month MARCH record # 6month APRIL record # 8

请帮帮我.....

Thanks in advance.....

SELECT * FROM table 
WHERE created_on in 
(select DISTINCT max(created_on) from table 
GROUP BY YEAR(created_on), MONTH(created_on))

根据迪尔的回答:

SELECT r.*
FROM table AS r
    JOIN (
        SELECT MAX(t.created_on) AS created_on
        FROM table AS t
        GROUP BY YEAR(t.created_on), MONTH(t.created_on)
    ) AS x USING (created_on)

请确保在created_on上有索引，否则，如果该表的行数超过几百行，该查询将终止您的数据库。

首先需要按年和月份进行分组(否则您将过滤掉其他年份中的月份)。使用MAX()获取每个组的最大日期。

SELECT *, MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on)

假设当天只有一条记录;

SELECT * from table where created_on IN (Select MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on) )