在没有子查询的 MySQL 中,ORDER BY 优先于 GROUP BY

发布于 2024-10-13 02:08:39 字数 635 浏览 7 评论 0原文

我有以下查询,它可以完成我想要的操作,但我怀疑可以在没有子查询的情况下执行此操作:

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

我需要的是按程序分组,但返回具有最高“ID”值的版本中的对象的结果。

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

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

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

当然不可能写成

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

谢谢。

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'

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

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

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.

Of course it is not possible to write

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

Thanks.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

撑一把青伞 2024-10-20 02:08:39

根据定义,ORDER BY 是在使用 GROUP BY 分组之后进行处理的。根据定义,处理任何 SELECT 语句的概念方式是:

  1. 计算 FROM 子句中引用的所有表的笛卡尔积 应用
  2. FROM 子句中的联接条件来过滤结果
  3. 应用 WHERE 子句中的过滤条件来进一步过滤结果
  4. 根据 GROUP BY 子句将结果分组为子集,将每个子集的结果折叠为一行,并计算任何聚合函数的值 - SUM()MAX()AVG() 等——对于每个这样的子集。请注意,如果未指定 GROUP BY 子句,则结果将被视为只有一个子集,并且任何聚合函数都适用于整个结果集,将其折叠为单行。
  5. 根据 HAVING 子句过滤现在分组的结果。
  6. 根据 ORDER BY 子句对结果进行排序。

当然,带有 GROUP BY 子句的 SELECT 结果集中允许的唯一列是

  • GROUP BY 子句中引用的
  • 列 聚合函数(例如 MAX())
  • 派生的文字/常量
  • 表达式来自上述任何一项。

只有损坏的 SQL 实现才允许诸如 select xxx,yyy,a,b,c FROM foo GROUP BY xxx,yyy 之类的操作 — 对 colulmsn a、b 和 c 的引用是无意义/未定义的,因为单独的组已折叠为一行,

By definition, ORDER BY is processed after grouping with GROUP BY. By definition, the conceptual way any SELECT statement is processed is:

  1. Compute the cartesian product of all tables referenced in the FROM clause
  2. Apply the join criteria from the FROM clause to filter the results
  3. Apply the filter criteria in the WHERE clause to further filter the results
  4. Group the results into subsets based on the GROUP BY clause, collapsing the results to a single row for each such subset and computing the values of any aggregate functions -- SUM(), MAX(), AVG(), etc. -- for each such subset. Note that if no GROUP BY clause is specified, the results are treated as if there is a single subset and any aggregate functions apply to the entire results set, collapsing it to a single row.
  5. Filter the now-grouped results based on the HAVING clause.
  6. Sort the results based on the ORDER BY clause.

The only columns allowed in the results set of a SELECT with a GROUP BY clause are, of course,

  • The columns referenced in the GROUP BY clause
  • Aggregate functions (such as MAX())
  • literal/constants
  • expresssions derived from any of the above.

Only broken SQL implementations allow things like select xxx,yyy,a,b,c FROM foo GROUP BY xxx,yyy — the references to colulmsn a, b and c are meaningless/undefined, given that the individual groups have been collapsed to a single row,

国产ˉ祖宗 2024-10-20 02:08:39

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

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

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
冰雪梦之恋 2024-10-20 02:08:39
SELECT  v.*
FROM    (
        SELECT  DISTINCT program
        FROM    versions
        ) vd
JOIN    versions v
ON      v.id = 
        (
        SELECT  vi.id
        FROM    versions vi
        WHERE   vi.program = vd.program
        ORDER BY
                vi.program DESC, vi.id DESC
        LIMIT 1
        )

(program, id) 上创建索引以使其快速运行。

关于您的原始查询:

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

此查询不会在除 MySQL 之外的任何 SQL 方言中进行解析。

它滥用了 MySQL 从 GROUP BY 语句返回未分组和未聚合表达式的能力。

SELECT  v.*
FROM    (
        SELECT  DISTINCT program
        FROM    versions
        ) vd
JOIN    versions v
ON      v.id = 
        (
        SELECT  vi.id
        FROM    versions vi
        WHERE   vi.program = vd.program
        ORDER BY
                vi.program DESC, vi.id DESC
        LIMIT 1
        )

Create an index on (program, id) for this to work fast.

Regarding your original query:

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

This query would not parse in any SQL dialect except MySQL.

It abuses MySQL's ability to return ungrouped and unaggregated expressions from a GROUP BY statement.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文