这里有很多类似的问题,但我认为没有一个能充分回答这个问题。

如果可以的话,我将从当前最流行的问题继续,用他们的例子。

这个实例中的任务是获取数据库中每个作者的最新文章。

示例查询产生了不可用的结果,因为它并不总是返回的最新帖子。

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

不幸的是,这个答案显然是错误的,在许多情况下产生的结果不如原始查询稳定。

我的最佳解决方案是使用表单的子查询

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,是该特定帖子的唯一标识符。


当前回答

以防万一是真的。我做过很多次这样的事情:

select * from 
  (select max(some_quantity) over (partition by id1, id2) as max_quantity, t.*
  from table_name t) tt 
where tt.max_quantity=tt.some_quantity;

这是具有字段some_quantity的条件最大值的分组。

其他回答

只需使用max函数和group函数

    select max(taskhistory.id) as id from taskhistory
            group by taskhistory.taskid
            order by taskhistory.datum desc

在子查询中使用ORDER BY并不是这个问题的最佳解决方案。

按author获取max(post_date)的最佳解决方案是使用子查询返回max日期,然后将其连接到post_author和max日期的表中。

解决方案应该是:

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

如果您有以下示例数据:

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')
;

子查询将返回的最大日期和作者:

MaxPostDate | Author
2/1/2013    | Jim

然后,由于您要将其连接回表,对于这两个值,您将返回该帖子的完整细节。

参见SQL Fiddle with Demo。

扩展我关于使用子查询准确返回此数据的评论。

MySQL不会强制您对SELECT列表中包含的每一列进行GROUP BY。因此,如果只GROUP BY一列,但总共返回10列,则无法保证返回的其他列值属于post_author。如果列不在GROUP BY中,MySQL选择应该返回什么值。

使用子查询和聚合函数将确保每次都返回正确的作者和帖子。

作为旁注,虽然MySQL允许您在子查询中使用ORDER BY,并允许您对SELECT列表中的每一列应用GROUP BY,但此行为在其他数据库(包括SQL Server)中是不允许的。

不。在分组之前对记录排序是没有意义的,因为分组将会改变结果集。子查询方式是首选方式。如果速度太慢,你就必须改变你的表设计,例如将每个作者的最后一篇文章的id存储在一个单独的表中,或者引入一个布尔列,为每个作者指出他的最后一篇文章。

不确定这是否已经被建议,但你现在可以使用SQL窗口函数:

SELECT * FROM (
      SELECT wp_posts.*, ROW_NUMBER() OVER (PARTITION BY wp_posts.post_author ORDER BY post_date DESC) rank
      FROM wp_posts
      WHERE wp_posts.post_status = 'publish'
      AND wp_posts.post_type = 'post'
  ) AS T
WHERE rank = 1

所有行都被“排名”,然后你只需要选择每个第一行。

我承认我对表现没有任何了解,但据我所知,这应该是可以接受的。

首先,不要在select中使用*,这会影响它们的性能并阻碍group by和order by的使用。 试试这个问题:

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

当你在ORDER BY中不指定表,只指定别名时,他们将对选择的结果进行排序。