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

时间:2021-11-07 12:11:53

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


2  

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


2  

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...

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