sql 只返回每个 id 的最大日期 [英] sql return only max date for each id

问题描述

我有一个数据库，里面有一张看起来像这样的表

I have a database with one table that looks like this

Books(id, title, author_id, date)

我在日期字段中使用类型 DATE.

I am using type DATE for the date field.

查询想要做的是只返回每个作者最近的书，因此即使对于具有相同 author_id 的书，也只返回具有该作者最近日期字段的书.因此，对于所有书籍，查找每个作者的最新书籍，返回标题、author_id 和日期.

What the query is looking to do is return only the most recent book from each author, so even for the books that have the same author_id, only return the book that has the most recent date field for that author. So for all books find the most recent book from each author, returning title, author_id, and date.

我相信我需要将 MAX 函数与 GROUP BY author_id 一起使用......或者可能包括一个 HAVING max(date)?在返回日期时，是否有可能将其发布到小数点后 1 位?(例如 3.1 如果预订日期刚好超过 3 岁?).

I believe I will need to use the MAX function with a GROUP BY author_id....or perhaps include a HAVING max(date)? Sort of a side note is it possible when returning the date to have it be age in years it has been released up to 1 decimal point? (ex. 3.1 if book date was just over 3 years old?).

推荐答案

您可以使用此查询:

SELECT *
FROM Books
WHERE (author_id, date) IN (SELECT author_id, MAX(date)
                            FROM Books
                            GROUP BY author_id)

子查询将为每个作者返回一本书的最大日期.外部查询将返回具有最大日期的每个作者的所有书籍.

subquery will return, for every author, the maximum date of a book. Outer query will return all books for every author with the maximum date.

请注意，如果作者在相同的最长时间出版，这可能会为每位作者返回不止一本书.

Please notice that this could return more than one book for every author if they are published in the same maximum date.

这篇关于sql 只返回每个 id 的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！