您可以使用此查询:

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.