SQL 组通过选择
这是我的代码：
mysql_query("SELECT * FROM messages WHERE to_user_id = '$user_id' GROUP BY from_user_id ORDER BY read,sent_date DESC")
这是我的表结构
我使用 GROUPY BY from_user_id 语句来简要显示“对话”列表，而不是每条消息。像这样
但是，正如您在图像中看到的，前两个方向错误，第一个说“1周前”，下面的说“2天前”。这些顺序错误的原因是 GROUP BY from_user_id 语句造成的。因为它将来自该用户的所有消息分组，并且不会包含最近的时间。
所以我的问题是：
如何 GROUP BY from_user_id 按最新记录？
Here is my code:
mysql_query("SELECT * FROM messages WHERE to_user_id = '$user_id' GROUP BY from_user_id ORDER BY read,sent_date DESC")
and here is my table structure
I use the GROUPY BY from_user_id statement to briefly show a list of "conversations" instead of every single message. Like this
But, as you can see in the image, the top two are the wrong way round, the first one says "1 week ago" and the one below says "2 days ago". The reason for these being in the wrong order is due to the GROUP BY from_user_id statement. Because it groups all the messages from that user and it wont have the the most recent time on it.
So my question is:
How can I GROUP BY from_user_id by the most recent record?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不能
SELECT未在GROUP BY中列出的列或允许的函数。这是GROUP BY的工作原理。尽管大多数其他 SQL 风格在此类查询上都会失败，但 MySQL 不会。相反，它提供任意结果 - 您所看到的。
您可以通过几种不同的方式解决这个问题。例如：
GROUP BY来SELECT最新用户对话的ids。JOIN一组ids，在一个查询中完成所有操作。You can not
SELECTcolumns not listed in theGROUP BYor otherwise allowed functions. This is howGROUP BYworks.Although most other SQL flavors would fail on such a query, MySQL doesn't. Instead it provides arbitrary results - what you are seeing.
You can solve this a few different ways. For example:
SELECTthe ids of the latest user conversations usingGROUP BYin a separate query.JOINthe set of ids.由于 MySQL 不支持像 ROW_NUMBER() OVER() 这样的窗口函数，您可以执行以下操作：
子查询将只返回每个用户的最新消息 id。我假设您的 auto_increment 与消息发送的顺序相对应。即使这不是您可能想要的确切逻辑，这是一种从 MySQL 中有效的分组记录中获取特定值子集的通用技术。
Since MySQL doesn't support windowing functions like
ROW_NUMBER() OVER()you can do something like this:The subquery will only return the newest message id for each user. I'm assuming your auto_increment corresponds with the order the messages are sent in. Even if it's not the exact logic you might want, this is a general technique to get a specific subset of values from grouped records that works in MySQL.
尝试用这个
Try with this