英文:
MySQL get records of last date of each month
问题
我需要根据数据库表中记录的日期获取每个月的最后日期的记录。现在，如果我使用以下查询从我的表中获取每个月的最后日期：
SELECT MAX(`date`) AS max_date
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`);
它将完美地返回每个月的最后日期，如下所示：
max_date
2023-01-31
2023-02-27
2023-03-31
2023-04-27
2023-05-31
2023-06-06
现在，如果我使用相同的查询来获取那个日期的其他数据，即使在上面的查询中使用了ORDER BY `date` DESC，它仅返回2023-01-31的记录。
以下是我尝试的查询：
SELECT * FROM cpr
INNER JOIN (
SELECT MAX(`date`) AS max_date
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
)
AS monthwise ON cpr.`date` = monthwise.max_date
另一个查询：
SELECT * FROM cpr
WHERE `date` IN (
SELECT MAX(`date`)
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
)
任何帮助将不胜感激。TIA 
英文:
I need to fetch records of last date of each month based on the dates of records in the database table. Now, if I fetch the last dates of each month in my table using following query:
SELECT MAX(`date`) AS max_date
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`);
It returns the last dates of each month perfectly as below:
max_date
2023-01-31
2023-02-27
2023-03-31
2023-04-27
2023-05-31
2023-06-06
Now, If I use the same query to get rest data on that date as following, it return only the records of 2023-01-31, even after using ORDER BY `date` DESC in above query.
Below are the queries I'm trying:
SELECT * FROM cpr
INNER JOIN (
SELECT MAX(`date`) AS max_date
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
)
AS monthwise ON cpr.`date` = monthwise.max_date
another one:
SELECT * FROM cpr
WHERE `date` IN (
SELECT MAX(`date`)
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
)
Any help will be appreciated. TIA
答案1
得分: 1
使用窗口函数！这比子查询和自连接通常更简单，而且更高效：
select *
from (
select c.*,
row_number() over(partition by year(date), month(date) order by date desc) rn
from cpr c
) c
where rn = 1
注意：窗口函数在MySQL版本8.0及以上可用。
英文:
Use window functions! This is simpler, and in general more efficient than a subquery and a self-join:
select *
from (
select c.*,
row_number() over(partition by year(date), month(date) order by date desc) rn
from cpr c
) c
where rn = 1
Note: window functions are available in MySQL starting version 8.0.
答案2
得分: 0
只需在以下SELECT语句中将*替换为cpr.*：
SELECT cpr.*
FROM cpr
INNER JOIN (
SELECT MAX(`date`) AS max_date
FROM cpr WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
) AS max_dates ON cpr.`date` = max_dates.max_date
英文:
Just need to replace * with cpr.* in SELECT statement as below:
SELECT cpr.*
FROM cpr
INNER JOIN (
SELECT MAX(`date`) AS max_date
FROM cpr WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`)
) AS max_dates ON cpr.`date` = max_dates.max_date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论