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...
我把复习日期按最近的每本书的顺序公布出来……