Mysql导出表order by

Question

如果我运行以下查询:

SELECT *
FROM `smp_data_log`
WHERE Post_id = 1234 AND Account_id = 1306
ORDER BY Created_time DESC

我返回 7 行，其中包括具有以下 Created_times 的条目:

1) 1424134801

2)1424134801

3) 1421802001

4) 3601

如果我运行以下查询:

SELECT mytable.*
FROM (SELECT * FROM `smp_data_log` ORDER BY Created_time DESC) AS mytable
WHERE Post_id = 1234 AND Account_id = 1306
GROUP BY Post_id

我希望看到 1424134801 作为单行返回 - 但我看到的是 3601？我本以为这会返回最晚的时间(按下降时间)。我做错了什么？

Answer 1

你的期望是错误的。 MySQL 中对此有详细记录。您正在使用一个扩展，其中 select 中的列不在 group by 中 - 这是一个非常坏的习惯，并且在其他数据库中不起作用(除非 ANSI 标准允许的一些非常特殊的情况)。

只需使用join即可获得您真正想要的内容:

SELECT l.*
FROM smp_data_log l JOIN
     (select post_id, max(created_time) as maxct
      from smp_data_log
      group by post_id
     ) lmax
     on lmax.post_id = l.post_id and lmax.maxct = l.created_time;

这是 documentation 的引用:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.