php mysql Group By以获取最新记录，而不是第一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13459516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
php mysql Group By to get latest record, not first record
提问by shanebp
The Table:
桌子：
(`post_id`, `forum_id`, `topic_id`, `post_time`)
(79, 8, 4, '2012-11-19 06:58:08');
(80, 3, 3, '2012-11-19 06:58:42'),
(81, 9, 9, '2012-11-19 06:59:04'),
(82, 11, 6, '2012-11-19 16:05:39'),
(83, 9, 9, '2012-11-19 16:07:46'),
(84, 9, 11, '2012-11-19 16:09:33'),
The Query:
查询：
SELECT post_id, forum_id, topic_id FROM posts
GROUP BY topic_id
ORDER BY post_time DESC
LIMIT 5
The Results:
结果：
[0] => [post_id] => 84 [forum_id] => 9 [topic_id] => 11
[1] => [post_id] => 82 [forum_id] => 11 [topic_id] => 6
[2] => [post_id] => 81 [forum_id] => 9 [topic_id] => 9
[3] => [post_id] => 80 [forum_id] => 3 [topic_id] => 3
[4] => [post_id] => 79 [forum_id] => 8 [topic_id] => 4
The Problem:
问题：
How to rewrite the query so that it returns post_id -> 83 instead of post_id -> 81 ?
如何重写查询，使其返回 post_id -> 83 而不是 post_id -> 81 ？
They both have the same forum and topic ids, but post_id -> 81 has an older date than post_id -> 83.
它们都具有相同的论坛和主题 ID，但 post_id -> 81 的日期比 post_id -> 83 的日期早。
But it seems that Group By gets the 'first' record and not the 'newest' one.
但似乎 Group By 获得了“第一”记录而不是“最新”记录。
I tried changing the query to
我尝试将查询更改为
SELECT post_id, forum_id, topic_id, MAX(post_time)
but that returns both post_id 81 and 83
但这会返回 post_id 81 和 83
回答by Terje D.
If you select attributes that are not used in the group clause, and are not aggregates, the result is unspecified. I.eyou don't know which rows the other attributes are selected from. (The sql standard does not allow such queries, but MySQL is more relaxed).
如果选择未在 group 子句中使用且不是聚合的属性，则结果未指定。即您不知道其他属性是从哪些行中选择的。（sql标准不允许这样的查询，但是MySQL比较宽松）。
The query should then be written e.g. as
然后应该将查询写成例如
SELECT post_id, forum_id, topic_id
FROM posts p
WHERE post_time =
(SELECT max(post_time) FROM posts p2
WHERE p2.topic_id = p.topic_id
AND p2.forum_id = p.forum_id)
GROUP BY forum_id, topic_id, post_id
ORDER BY post_time DESC
LIMIT 5;
or
或者
SELECT post_id, forum_id, topic_id FROM posts
NATURAL JOIN
(SELECT forum_id, topic_id, max(post_time) AS post_time
FROM posts
GROUP BY forum_id, topic_id) p
ORDER BY post_time
LIMIT 5;
回答by Alvar FinSoft Soome
It's not very beautiful , but it works:
它不是很漂亮，但它有效：
SELECT * FROM (SELECT post_id, forum_id, topic_id FROM posts
ORDER BY post_time DESC) as temp
GROUP BY topic_id
回答by NappingRabbit
try something like
尝试类似
SELECT post_id, forum_id, topic_id
FROM (SELECT post_id, forum_id, topic_id
FROM posts
ORDER BY post_time DESC)
GROUP BY topic_id
ORDER BY topic_id desc
LIMIT 0,5
change the order byand limitas needed.
根据需要更改order by和limit。
回答by Grégtheitroade Lafortune
Maybe not the best way of doing it but sometimes the function group_concat()can be userfull, it will return a string of all aggregated values sorted like you want and seperated by comma (coupled value are separated by space). I then use the function SPLIT_STRING() to cut the first id in the string.
也许不是最好的方法，但有时函数group_concat()可以是 userfull，它会返回所有聚合值的字符串，这些值按您的需要排序并用逗号分隔（耦合值用空格分隔）。然后我使用函数 SPLIT_STRING() 来剪切字符串中的第一个 id。
SELECT
post_id,
SPLIT_STRING( group_concat( forum_id, post_time ORDER BY post_time DESC ) ,' ',1 )as forum_id,
SPLIT_STRING( group_concat( topic_id, post_time ORDER BY post_time DESC ) ,' ',1 )as topic_id ,
FROM posts
GROUP BY topic_id
ORDER BY post_time DESC
LIMIT 5
So the aggregated forum_id, post_time will be like this :
所以聚合的 forum_id, post_time 将是这样的：
81 2012-11-19 06:59:04,83 2012-11-19 16:07:46
81 2012-11-19 06:59:04,83 2012-11-19 16:07:46
So you need to work with a string representation of integers and datetime couples, each couples separated by comma so I used this function to get the first INT :
所以你需要使用整数和日期时间对的字符串表示，每对用逗号分隔，所以我使用这个函数来获得第一个 INT ：
CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
delim, '');
Note : function SPLIT_STRING(str, delim, pos) was found here : Equivalent of explode() to work with strings in MySQL
注意：在这里找到了函数 SPLIT_STRING(str, delim, pos) ：相当于explode() 在 MySQL 中处理字符串
回答by Vikash
This will also work fine for you.
这对你来说也很好用。
SELECT *
FROM (
SELECT post_id, forum_id, topic_id FROM posts
ORDER BY post_time DESC
LIMIT 5
) customeTable
GROUP BY topic_id
