ORDER BY子查询，用于GROUP BY到JOIN的转换 [英] ORDER BY Subquery for GROUP BY to JOIN conversion

问题描述

我有这张桌子

id |   title   | amount | timestamp
1  |  random1  |   150  | 1313635011
2  |  random2  |   190  | 1313635730
3  |  random2  |   210  | 1313637359
4  |  random2  |   100  | 1313691807
5  |  random3  |   130  | 1313692673
6  |  random4  |   900  | 1313692739
7  |  random4  |   111  | 1313692988

我想得到这个结果(行名和时间戳最大的行):

i want to get this result (rows with different titles and biggest timestamps):

id |   title   | amount | timestamp
1  |  random1  |   150  | 1313635011
4  |  random2  |   100  | 1313691807
5  |  random3  |   130  | 1313692673
7  |  random4  |   111  | 1313692988

我有这个查询.

SELECT * FROM (
  SELECT * FROM table ORDER BY timestamp DESC 
) m GROUP BY title

它起到了魅力的作用，但是可以将其转换为JOIN语句吗?

It works as charm but can this be converted to a JOIN statement?

谢谢

推荐答案

这可以简化为以下内容(子查询中的ORDER BY是无用的):

This can be simplified to the following (the ORDER BY in the subquery is useless):

SELECT * 
FROM table
GROUP BY title

为什么您认为需要JOIN? (好的，这已经通过评论解决了.)

Why do you think that you need JOIN? (Ok, this was resolved by comments).

在对每个标题(具有最大时间戳记的行)进行注释后，便可以完成工作:

After your comment that you need for every title, the row with biggest timestamp, this would do the work:

SELECT t.* 
FROM
    table AS t
  JOIN
    ( SELECT title
           , MAX(timestamp) AS maxts
      FROM table
      GROUP BY title
    ) AS grp
    ON grp.title = t.title
    AND grp.maxts = t.timestamp
ORDER BY t.timestamp DESC

---

作为记录，您的原始查询:

---

For the record, your original query:

SELECT * 
FROM 
  ( SELECT * 
    FROM table 
    ORDER BY timestamp DESC 
  ) m
GROUP BY title

可能可以按预期工作，但是:MySQL中仅 ，允许您在GROUP BY子句中未使用的SELECT列表字段中使用(或取决于那些)，其中没有任何聚合函数.因此，上述查询将为每个标题返回或多或少随机行.实际上，它将返回找到标题的第一行.因此，首先运行子查询(按timestamp DESC排序)将首先找到时间戳最大的行.

might work as expected, but: only in MySQL that allows you to use in the SELECT list fields that are not in the GROUP BY clause (or depend on those), without any aggregate functions in them. So, the above query will return a more or less random row for every title. In fact, it will return the first row it will find for a title. So, having first run the subquery (which orders by timestamp DESC) results in finding first the row with biggest timestamp.

然而，这仅是因为(当，如果)优化器不了解子查询是无用的而发生.一天升级到MySQL 7.5版后，您的原始查询可以正常运行，并且查询像以前一样停止工作. (因为优化器变得更聪明，并且无需子选择即可将查询翻译为更简单的查询.)

This however, only happens because (when, if) the optimizer does not understand that the subquery is useless. You may find that your original query runs fine when one day you upgrade to MySQL version 7.5 and your query stops working as before. (because the optimizer got more clever and translated your query to a simpler without sub-select).

如果MySQL在将来的版本中决定使用GROUP BY查询的SQL标准，您甚至可能发现查询完全停止工作并产生错误.

You may even find your query to stop working altogether and produce error if MySQL decides in a future release to be in terms with SQL standards for the GROUP BY queries.

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