通过查询获取MySQL组以显示该组中具有最高值的行 [英] Getting a MySQL group by query to display the row in that group with the highest value

问题描述

我正在尝试找出如何查询数据库的方法，这样它实际上将首先对我的结果进行排序，然后对它们进行分组...这个问题似乎有点普遍，我已经找到了示例，但我仍然不太了解掌握操作方法"并在我自己的情况下使用示例....因此，我们非常感谢所有帮助.

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.

这是我的MySQL表:

Here are my MySQL tables:

书籍
book_id
book_title

books
book_id
book_title

用户
user_id
用户名

users
user_id
user_name

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

book_reviews
review_id
book_id
user_id
review_date (unix timestamp date)

我想查询30条最新书评.它们将简单显示为:
书名
审阅者的用户名

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

不过，我希望每本书最多显示一次.因此，列表中显示的评论应该是最近添加的评论.为此，我只是按book_name分组，并按review_date DESC排序.但是以这种方式查询不会显示最新添加的review_date作为按行分组的记录，因此我的数据不正确.

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.

这是我当前的查询:

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

从我阅读的内容来看，好像必须要有一个子查询，才能获得MAX(review_date)值，但我仍然不知道如何将其全部链接起来.

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.

一吨.

推荐答案

使用:

  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不具有分析/排名/窗口功能，但这会将评论的最新等级标记为1.这是每本书的基础...

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

我按书中最新的顺序列出了检查日期，以按顺序排列...

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

这篇关于通过查询获取MySQL组以显示该组中具有最高值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！