MySQL中为什么GROUP BY前可以使用ORDER BY，以及解决方法

在一般的SQL语句中，对于这个问题的答案是不可以，但是由于MySQL中的GROUP BY模式相对宽松，所以答案是可以的。

假设你有一个BTREE索引(post_status, post_type, post_author, post_date)。在底层，这个索引是什么样子的呢？

(post_status='publish', post_type='post', post_author='user A', post_date='2012-12-01')

(post_status='publish', post_type='post', post_author='user A', post_date='2012-12-31')

(post_status='publish', post_type='post', post_author='user B', post_date='2012-10-01')

(post_status='publish', post_type='post', post_author='user B', post_date='2012-12-01')

数据按照这些字段的升序排列。

当你使用GROUP BY时，默认情况下它会按照分组字段(post_author，在我们的例子中；post_status, post_type是由于WHERE子句的要求)对数据进行排序，并且如果有匹配的索引，它会取每个分组中的第一条数据，按照升序排列。也就是说，查询会得到以下结果(每个用户的第一篇文章)：

(post_status='publish', post_type='post', post_author='user A', post_date='2012-12-01')

(post_status='publish', post_type='post', post_author='user B', post_date='2012-10-01')

但是MySQL中的GROUP BY允许你显式地指定排序方式。当你按照post_author的降序请求时，它将会按照相反的顺序遍历我们的索引，仍然取每个分组的第一条记录，但实际上是最后一条记录。

也就是说

...
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author DESC

会得到

(post_status='publish', post_type='post', post_author='user B', post_date='2012-12-01')

(post_status='publish', post_type='post', post_author='user A', post_date='2012-12-31')

现在，当你对分组的结果按照post_date排序时，你将得到想要的数据。

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

注意：

对于这个特定的查询，这不是我推荐的方法。在这种情况下，我会使用提供的稍微修改过的版本。但是这个技巧可能非常有用。请看我在这里的回答：检索每个分组中的最后一条记录

潜在的问题：这种方法的缺点是

• 查询的结果取决于索引，这与SQL的精神是相悖的（索引只应该加快查询）；
• 索引对查询的影响一无所知（你或者将来的某个人可能会发现索引过于耗费资源，并进行一些更改，从而破坏查询结果，而不仅仅是性能）
• 如果你不理解查询的工作原理，很可能过一段时间你会忘记这个解释，然后这个查询会使你和你的同事感到困惑。

优点是在复杂情况下提高了性能。在这种情况下，查询的性能应该与的查询相同，因为涉及到大量的数据排序（所有数据都加载到临时表中，然后进行排序；顺便说一句，他的查询也需要(post_status, post_type, post_author, post_date)索引）。

我的建议：

正如我所说的，这些查询会使MySQL浪费时间在临时表中对可能巨大的数据进行排序。如果需要分页（涉及到LIMIT），大部分数据甚至会被丢弃。我会尽量减少排序数据的数量：也就是在子查询中对最小的数据进行排序和限制，然后与整个表进行连接。

SELECT * 
FROM wp_posts
INNER JOIN
(
  SELECT max(post_date) post_date, post_author
  FROM wp_posts
  WHERE post_status='publish' AND post_type='post'
  GROUP BY post_author
  ORDER BY post_date DESC
  -- LIMIT放在这里
) p2 USING (post_author, post_date)
WHERE post_status='publish' AND post_type='post';

使用上述方法的同样查询：

SELECT *
FROM (
  SELECT post_id
  FROM wp_posts
  WHERE post_status='publish' AND post_type='post'
  GROUP BY post_author DESC
  ORDER BY post_date DESC
  -- LIMIT放在这里
) as ids
JOIN wp_posts USING (post_id);

所有这些查询及其执行计划可以在SQLFiddle上查看。

这是一个有趣的技巧。两件事：你说不要在家里尝试，潜在的陷阱是什么？其次，你提到了对bluefeet的答案稍作修改，那会是什么？

谢谢你的回答，很有趣地看到有人以不同的方式解决这个问题。由于我的数据集远远不及你的1800万+行，所以我认为性能不像可维护性那样重要，所以我认为后面的选项可能更合适。我喜欢子查询中的限制的想法。

MySQL中的order by在group by之前的问题产生的原因是MySQL在使用group by扩展的时候，允许对一些字段进行分组（在这种情况下只有post_author），并且选择非聚合的列（即没有在group by子句中列出的列，也没有在聚合函数中使用的列，例如MIN、MAX、COUNT等）。

这种扩展的正确用法是当非聚合列的所有值对于每一行都相等时，这样的情况下是有用的。如果需要提取在一个花园中生长的所有花朵，但是在一个花园中有多种花朵生长，可以使用子查询来实现。

如果需要提取那些是花园中唯一花朵的所有花朵，可以将HAVING条件改为HAVING COUNT(DISTINCT flower)=1，但是MySQL也允许使用以下方式：

SELECT   GardensFlowers.*
FROM     GardensFlowers
GROUP BY name
HAVING   COUNT(DISTINCT flower)=1;

虽然这不是标准SQL，但更简单。

然而，当选择非聚合列的值不相同时，MySQL选择的值是不确定的。因此，在进行GROUP BY之前对查询进行排序是无法解决这个问题的。

为了确保MySQL选择的是期望的第一个值，需要对查询进行排序，并使用子查询。以下是一个可能的解决方法：

SELECT wp_posts.*
FROM   wp_posts
WHERE  id IN (
  SELECT max(id)
  FROM wp_posts
  WHERE (post_author, post_date) = (
    SELECT   post_author, max(post_date)
    FROM     wp_posts
    WHERE    wp_posts.post_status='publish'
             AND wp_posts.post_type='post'
    GROUP BY post_author
  ) AND wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
  GROUP BY post_author
)

在内部查询中，返回每个作者的最大发布日期。然后考虑到同一个作者理论上可能同时拥有两篇文章，所以只获取最大ID。然后返回具有这些最大ID的所有行。这个查询可以使用JOIN来提高性能。

总之，正确使用group by扩展和解决order by在group by之前的方法是通过使用子查询，对查询进行排序，并仔细阅读MySQL的文档以了解其行为。

MySQL中的"ORDER BY"在"GROUP BY"之前的问题是因为MySQL允许在子查询中使用"ORDER BY"，而其他数据库产品如SQL Server则不允许。然而，使用"ORDER BY"在子查询中可能会导致错误的结果。为了解决这个问题，可以使用子查询返回每个作者的最大日期，并将其与表进行连接，连接条件是"post_author"和最大日期。以下是解决方案的示例代码：

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

这个查询将返回每个作者的最新文章。当你在子查询中使用"ORDER BY"时，你可以确保每次执行查询时都能返回正确的结果。另外，这个查询在过滤数据时使用了两个条件，一个是"post_status"，一个是"post_type"，这样可以确保只返回已发布的文章。这个查询的效率可能会比仅使用子查询要稍微慢一些，但它能确保返回正确的结果。