通过查询获取MySQL组，以显示该组中值最高的行

I'm trying to figure out how to query my database so that it will essentially first ORDER my results and then GROUP them... This question seems to be slightly common and I have found examples but I still don't quite grasp the 'how' to do this and use the examples in my own situation.... So all help is definitely appreciated.

我想弄清楚如何查询我的数据库，这样它会首先对我的结果排序，然后对它们进行分组……这个问题似乎有点共同的和我发现的例子但我仍然不太把握“如何”这样做,使用....例子在我自己的情况所以所有的帮助都是肯定的。

Here are my MySQL tables:

下面是我的MySQL表:

books
book_id
book_title

书book_id book_title

users
user_id
user_name

用户user_id user_name

book_reviews
review_id
book_id
user_id
review_date (unix timestamp date)

book_reviews review_id book_id user_id review_date (unix时间戳日期)

I would like to query 30 of the latest book reviews. They will simply display as:
Book Name
Username of Reviewer

我想查询30个最新的书评。它们将简单地显示为:Reviewer的图书名用户名

However I would like to display each book no more than one time. So the review shown in the list should be the most recently added review. To do this I have been simply grouping by book_name and ordering by review_date DESC. But querying this way doesn't display the record with the most recently added review_date as the grouped by row so my data is incorrect.

然而，我想展示每本书不超过一次。所以列表中显示的评论应该是最近添加的评论。为此，我只是按book_name进行分组，并按review_date DESC进行排序，但是以这种方式进行查询时，不会以最近添加的review_date作为分组的行，因此我的数据是不正确的。

Here is my current query:

以下是我当前的问题:

SELECT books.books_title, users.user_name, book_reviews.review_id FROM books, users, book_reviews WHERE book_reviews.book_id = books.book_id AND book_reviews.user_id = users.user_id GROUP BY book_title ORDER BY review_date DESC LIMIT 30

From what I've read it seems like I have to have a subquery where I get the MAX(review_date) value but I still don't understand how to link it all up.

从我所读到的内容来看，我似乎必须要有一个子查询才能获得MAX(review_date)值，但我还是不明白如何将它链接起来。

Thanks a ton.

由于一吨。

1 个解决方案

#1

Use:

使用:

  SELECT x.book_title,
         x.user_name
    FROM (SELECT b.book_title,
                 u.user_name,
                 br.review_date,
                 CASE
                   WHEN @book = b.book_title THEN @rownum := @rownum + 1
                   ELSE @rownum := 1
                 END AS rank,
                 @book := b.book_title
            FROM BOOKS b
            JOIN BOOK_REVIEWS br ON br.book_id = b.book_id
            JOIN USERS u ON u.user_id = br.user_id
            JOIN (SELECT @rownum := 0, @book := '') r
        ORDER BY b.book_title, br.review_date DESC) x
   WHERE x.rank = 1
ORDER BY x.review_date DESC
   LIMIT 30

MySQL doesn't have analytical/ranking/windowing functionality, but this ranks the reviews where the latest is marked as 1. This is on a per book basis...

MySQL没有分析/排名/窗口功能，但是它会将最近的评论标记为1。这是按每本书计算的……

I exposed the review date to order by the latest of those which are the latest per book...

我把复习日期按最近的每本书的顺序公布出来……

#1