MySQL 选择 GROUP BY 顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8556283/
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
MySQL select GROUP BY order
提问by puks1978
I have a mysql statement
我有一个 mysql 语句
SELECT *
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
GROUP BY from_user_id
ORDER BY date_sent DESC
and it is producing the correct results however they are not in the correct order.
并且它产生了正确的结果，但是它们的顺序不正确。
The grouping works well but it record displayed in the group is the first recorded entered into the DB but I would like the latest record to be displayed in each group.
分组运行良好，但组中显示的记录是输入数据库的第一个记录，但我希望在每个组中显示最新记录。
Is there a way to have the latest record displayed for each group?
有没有办法为每个组显示最新记录？
2011-12-19 12:16:25 This is the first message
2011-12-19 12:18:20 This is the second message
2011-12-19 12:43:04 This is the third message
The group shows 'This is the first message' where I would like 'This is the third message' as that is the most recent record/message.
该组显示“这是第一条消息”，其中我想要“这是第三条消息”，因为这是最新的记录/消息。
Cheers
干杯
采纳答案by ypercube??
This may work (but not guaranteed):
这可能有效（但不能保证）：
SELECT *
FROM
( SELECT *
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
ORDER BY date_sent DESC
) tmp
GROUP BY from_user_id
ORDER BY date_sent DESC
This should work:
这应该有效：
SELECT t.*
FROM
tbl_messages AS t
JOIN
( SELECT from_user_id
, MAX(date_sent) AS max_date_sent
FROM tbl_messages
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id'
GROUP BY from_user_id
) AS tg
ON (tg.from_user_id, tg.max_date_sent) = (t.from_user_id, t.date_sent)
ORDER BY t.date_sent DESC
回答by 11101101b
Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY like this:
通过像这样用 GROUP BY 包装您的查询，在 ORDER BY 之后执行 GROUP BY：
SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from
回答by DRapp
If your messages table has a primary key that is auto-increment, and all messages are by nature highest number is the most recent date... However, since I don't KNOW that, I am going based on the MAX( date_sent ) instead of max( SomeIDKey ), but the principle is the same.
如果您的消息表有一个自动递增的主键，并且所有消息本质上都是最高数字是最近的日期......但是，由于我不知道，我将基于 MAX( date_sent )而不是 max( SomeIDKey )，但原理是一样的。
select
tm2.*
from
( select tm1.from_user_id,
max( tm1.date_sent ) LatestMsgDate
from tbl_messages tm1
group by tm1.from_user_id ) MaxPerUser
left join tbl_messages tm2
on MaxPerUser.From_User_ID = tm2.From_User_ID
AND MaxPerUser.LatestMsgDat = tm2.Date_Sent
order by
date_sent DESC
回答by Sudhir Bastakoti
Do you mean something like this:
你的意思是这样的：
SELECT * FROM tbl_messages WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' GROUP BY from_user_id ORDER BY from_user_id, date_sent DESC
