問題描述

這里有很多類似的問題，但我認為沒有人能充分回答這個問題.

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately.

我將從當前最流行的問題繼續，并使用他們的例子，如果沒關系.

I'll continue from the current most popular question and use their example if that's alright.

此實例中的任務是獲取數據庫中每個作者的最新帖子.

The task in this instance is to get the latest post for each author in the database.

示例查詢產生了無法使用的結果，因為它并不總是返回最新的帖子.

The example query produces unusable results as its not always the latest post that is returned.

SELECT wp_posts.* FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author           
    ORDER BY wp_posts.post_date DESC

當前接受的答案是

SELECT
    wp_posts.*
FROM wp_posts
WHERE
    wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
HAVING wp_posts.post_date = MAX(wp_posts.post_date) <- ONLY THE LAST POST FOR EACH AUTHOR
ORDER BY wp_posts.post_date DESC

不幸的是，這個答案是簡單明了的錯誤，在許多情況下產生的結果不如原始查詢穩定.

Unfortunately this answer is plain and simple wrong and in many cases produces less stable results than the orginal query.

我最好的解決方案是使用表單的子查詢

My best solution is to use a subquery of the form

SELECT wp_posts.* FROM 
(
    SELECT * 
    FROM wp_posts
    ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author 

我的問題很簡單:無論如何在分組之前對行進行排序而不訴諸子查詢?

編輯:這個問題是另一個問題的延續，我的具體情況略有不同.您可以(并且應該)假設還有一個 wp_posts.id 是該特定帖子的唯一標識符.

Edit: This question was a continuation from another question and the specifics of my situation are slightly different. You can (and should) assume that there is also a wp_posts.id that is a unique identifier for that particular post.

推薦答案

在子查詢中使用 ORDER BY 并不是解決此問題的最佳方法.

Using an ORDER BY in a subquery is not the best solution to this problem.

按作者獲取 max(post_date) 的最佳解決方案是使用子查詢返回最大日期，然后將其加入到 post_author 上的表中和最大日期.

The best solution to get the max(post_date) by author is to use a subquery to return the max date and then join that to your table on both the post_author and the max date.

解決方案應該是:

SELECT p1.* 
FROM wp_posts p1
INNER JOIN
(
    SELECT max(post_date) MaxPostDate, post_author
    FROM wp_posts
    WHERE post_status='publish'
       AND post_type='post'
    GROUP BY post_author
) p2
  ON p1.post_author = p2.post_author
  AND p1.post_date = p2.MaxPostDate
WHERE p1.post_status='publish'
  AND p1.post_type='post'
order by p1.post_date desc

如果您有以下示例數據:

If you have the following sample data:

CREATE TABLE wp_posts
    (`id` int, `title` varchar(6), `post_date` datetime, `post_author` varchar(3))
;

INSERT INTO wp_posts
    (`id`, `title`, `post_date`, `post_author`)
VALUES
    (1, 'Title1', '2013-01-01 00:00:00', 'Jim'),
    (2, 'Title2', '2013-02-01 00:00:00', 'Jim')
;

子查詢將返回最大日期和作者:

The subquery is going to return the max date and author of:

MaxPostDate | Author
2/1/2013    | Jim

然后，由于您將其加入到表格中，因此您將在這兩個值上返回該帖子的完整詳細信息.

Then since you are joining that back to the table, on both values you will return the full details of that post.

參見SQL Fiddle with Demo.

擴展我關于使用子查詢準確返回此數據的評論.

To expand on my comments about using a subquery to accurate return this data.

MySQL 不會強制您GROUP BY 包含在SELECT 列表中的每一列.因此，如果您只GROUP BY 一列但總共返回10 列，則無法保證返回屬于post_author 的其他列值.如果該列不在 GROUP BY 中，MySQL 將選擇應返回的值.

MySQL does not force you to GROUP BY every column that you include in the SELECT list. As a result, if you only GROUP BY one column but return 10 columns in total, there is no guarantee that the other column values which belong to the post_author that is returned. If the column is not in a GROUP BY MySQL chooses what value should be returned.

使用帶有聚合函數的子查詢將保證每次返回正確的作者和帖子.

Using the subquery with the aggregate function will guarantee that the correct author and post is returned every time.

作為旁注，雖然 MySQL 允許您在子查詢中使用 ORDER BY 并允許您將 GROUP BY 應用于不是 中的每一列>SELECT 列出此行為在包括 SQL Server 在內的其他數據庫中是不允許的.

As a side note, while MySQL allows you to use an ORDER BY in a subquery and allows you to apply a GROUP BY to not every column in the SELECT list this behavior is not allowed in other databases including SQL Server.

這篇關于MySQL order by before group by的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！