在没有子查询的 MySQL 中，ORDER BY 优先于 GROUP BY [英] Give priority to ORDER BY over a GROUP BY in MySQL without subquery

问题描述

我有以下查询可以满足我的要求，但我怀疑没有子查询也可以做到这一点:

I have the following query which does what I want, but I suspect it is possible to do this without a subquery:

  SELECT * 
    FROM (SELECT * 
            FROM 'versions' 
        ORDER BY 'ID' DESC) AS X 
GROUP BY 'program'

我需要的是按程序分组，但返回ID"值最高的版本中的对象的结果.

What I need is to group by program, but returning the results for the objects in versions with the highest value of "ID".

根据我过去的经验，像这样的查询应该在 MySQL 中工作，但由于某种原因，它不是:

In my past experience, a query like this should work in MySQL, but for some reason, it's not:

  SELECT * 
    FROM 'versions' 
GROUP BY 'program' 
ORDER BY MAX('ID') DESC

我想要做的是让 MySQL 先执行 ORDER BY，然后执行 GROUP BY，但它坚持先执行 GROUP BY，然后再执行 ORDER BY.即它是对分组的结果进行排序，而不是对排序的结果进行分组.

What I want to do is have MySQL do the ORDER BY first and then the GROUP BY, but it insists on doing the GROUP BY first followed by the ORDER BY. i.e. it is sorting the results of the grouping instead of grouping the results of the ordering.

当然不能写

SELECT * FROM 'versions' ORDER BY 'ID' DESC GROUP BY 'program'

谢谢.

推荐答案

只要 (program,id) 上有一个复合索引，这应该可以做到并且工作得很好.子查询应该只检查每个程序分支的第一个 id，并从外部查询中快速检索所需的记录.

This should do it and work pretty well as long as there is a composite index on (program,id). The subquery should only inspect the very first id for each program branch, and quickly retrieve the required record from the outer query.

select v.*
from
(
    select program, MAX(id) id
    from versions
    group by program
) m
inner join versions v on m.program=v.program and m.id=v.id

这篇关于在没有子查询的 MySQL 中，ORDER BY 优先于 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！