MySQL在分组前按顺序排序

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

我将继续从目前最流行的问题，并使用他们的例子，如果可以的话。

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

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

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

420571 次浏览
小开
首先，不要在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中不指定表，只指定别名时，他们将对选择的结果进行排序。

小开

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

小开
最佳答案

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

通过author获取max(post_date)的最佳解决方案是使用子查询返回最大日期，然后在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

如果您有以下示例数据:

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演示。

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

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

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

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

小开

试试这个。只需要从每个作者那里获得最新发布日期的列表。这是它

SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post' AND wp_posts.post_date IN(SELECT MAX(wp_posts.post_date) FROM wp_posts GROUP BY wp_posts.post_author)
小开

你的解决方案使用了一个扩展到GROUP BY子句，它允许根据某些字段进行分组(在这种情况下，只是post_author):

GROUP BY wp_posts.post_author

并选择非聚合列:

SELECT wp_posts.*

不在group by子句中列出，或者不在聚合函数中使用(MIN、MAX、COUNT等)。

正确使用GROUP BY子句的扩展

当非聚合列的所有值对每一行都相等时，这很有用。

例如，假设你有一个表GardensFlowers(花园的name，花园中生长的flower):

INSERT INTO GardensFlowers VALUES
('Central Park',       'Magnolia'),
('Hyde Park',          'Tulip'),
('Gardens By The Bay', 'Peony'),
('Gardens By The Bay', 'Cherry Blossom');

你想要提取花园中生长的所有花，那里有很多花。然后你必须使用子查询，例如你可以使用这个:

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

如果你需要提取花园中唯一的花，你可以将HAVING条件改为HAVING COUNT(DISTINCT flower)=1，但MySql也允许你这样做:

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

没有子查询，不是标准SQL，但更简单。

GROUP BY子句扩展名使用不当

但是，如果SELECT每一行都不相等的非聚合列会发生什么?MySql为该列选择的值是什么?

看起来MySql总是选择它遇到的第一个值。

为了确保它遇到的第一个值恰好是你想要的值，你需要对有序查询应用GROUP BY，因此需要使用子查询。否则你不能这样做。

假设MySql总是选择它遇到的第一行，那么正确地对GROUP BY之前的行进行排序。但不幸的是，如果你仔细阅读文档，你会发现这个假设是不正确的。

当选择不总是相同的非聚合列时，MySql可以自由选择任何值，因此它实际显示的结果值是不确定的.;

我发现这种获取非聚合列的第一个值的技巧被大量使用，而且它通常/几乎总是有效，我有时也使用它(自担风险)。但是由于没有记录，所以不能依赖这种行为。

这个链接(感谢ypercube!) GROUP BY技巧被优化掉了显示了相同的查询在MySql和MariaDB之间返回不同结果的情况，可能是因为不同的优化引擎。

所以，如果这个把戏成功了，只是运气的问题。

接受另一个问题的答案在我看来是错误的:

HAVING wp_posts.post_date = MAX(wp_posts.post_date)

wp_posts.post_date是一个未聚合的列，它的值将是官方未确定的，但它可能是遇到的第一个post_date。但由于GROUP BY技巧应用于无序表，因此无法确定遇到的第一个post_date是哪一个。

它可能会返回某个作者的唯一帖子，但这也不总是确定的。

一个可能的解决方案

我认为这是一个可能的解决方案:

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值最大的行。使用连接而不是IN子句可以使它更快。

(如果你确定ID只增加，如果ID1 > ID2也意味着post_date1 > post_date2，那么查询可以变得更简单，但我不确定情况是否如此)。

小开

简单回顾一下，标准的解决方案使用了一个不相关的子查询，看起来像这样:

SELECT x.*
FROM my_table x
JOIN (SELECT grouping_criteria,MAX(ranking_criterion) max_n FROM my_table GROUP BY grouping_criteria) y
ON y.grouping_criteria = x.grouping_criteria
AND y.max_n = x.ranking_criterion;

如果你正在使用一个古老版本的MySQL，或者一个相当小的数据集，那么你可以使用以下方法:

SELECT x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.joining_criteria = x.joining_criteria
AND y.ranking_criteria < x.ranking_criteria
WHERE y.some_non_null_column IS NULL;
小开

你将要读到的内容相当俗气，所以不要在家尝试!

在SQL中，你的问题的答案通常是没有，但由于GROUP BY的宽松模式(由@bluefeet提到)，在MySQL中答案是是的。

假设，你有一个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_user时，它将以相反的顺序遍历索引，仍然为每一组取实际上是最后一组的第一条记录。

这是

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

注:

对于这个特定的查询，我不建议使用这种方法。在这种情况下，我会使用@bluefeet建议的稍微修改的版本。但是这个技巧可能非常有用。看看我的答案:检索每个组中的最后一条记录

陷阱:这种方法的缺点是

  • 查询的结果取决于索引，这违背了SQL的精神(索引应该只加速查询);
  • 索引不知道它对查询的影响(您或其他人将来可能会发现索引太消耗资源，并以某种方式更改它，破坏查询结果，而不仅仅是它的性能)
  • 如果您不理解查询是如何工作的，很可能在一个月内就会忘记解释，查询将使您和您的同事感到困惑。

其优势在于在困难情况下的性能。在这种情况下，查询的性能应该与@bluefeet的查询相同，因为涉及到排序的数据量(所有数据都加载到一个临时表中，然后排序;顺便说一句，他的查询也需要(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 GOES HERE
) 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 GOES HERE
) as ids
JOIN wp_posts USING (post_id);

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

小开

**子查询在使用大型数据集时可能会对性能产生不良影响**

原始查询

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 p.post_status,
p.post_type,
Max(p.post_date),
p.post_author
FROM   wp_posts P
WHERE  p.post_status = "publish"
AND p.post_type = "post"
GROUP  BY p.post_author
ORDER  BY p.post_date;

因为我在select clause ==> max(p.post_date)中使用max，因此可以避免子选择查询和按组后的max列排序。

小开

只需使用max函数和group函数

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

不确定这是否已经被建议过，但你现在可以使用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 * 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的条件最大值的分组。

小开

以下是我使用用户定义变量获得一致结果的解决方案，甚至没有GROUP BY。我们的目标是获取整个行，而不仅仅是一行中一个单元格的最大值。请看下面的例子:

SET @product_id := 0;


SELECT
products.order_code,
purchases.`date`,
purchases.price
FROM products
LEFT JOIN (
SELECT
purchases.`date`,
purchases.price,
IF(@product_id = purchases.product_id, 0, 1) AS is_last,
@product_id := purchases.product_id AS product_id
FROM purchases
ORDER BY purchases.product_id ASC, purchases.id DESC
) purchases ON products.id = purchases.product_id
WHERE purchases.is_last = 1
ORDER BY products.order_code ASC;

我不确定性能如何，但在50000行购买表上它是0.1秒。如果我能做些什么改进，请告诉我。


提交答案