php Mysql 对用户消息进行分组和排序

发布于 2024-12-04 07:47:32 字数 926 浏览 1 评论 0原文

PHP Mysql 表：消息

id   sender   receiver      time_sent          Message  SendDel RecDel
1     1          3      2011-08-17 14:00:00     [text]     0      0
2     3          1      2011-08-17 15:00:00     [text]     0      0
3     2          4      2011-08-18 14:19:28     [text]     1      0
4     4          2      2011-08-18 15:19:28     [text]     0      0

目标是检索最高值消息（MAX）并根据发送者、接收者对消息进行分组。因此，消息 id 的 1 和 2 将组合在一起，而 id 的 3 和 4 将组合在一起。

示例：登录 userid = 2 应该返回

id   sender   receiver      time_sent          Message  SendDel RecDel
4     4          2      2011-08-18 15:19:28     [text]     0      0

我不确定为什么，但我的查询没有将所有消息分组在一起。这是我的疑问：

SELECT id, sender, receiver, MAX(time_sent), MAX(message) 
FROM Messages
WHERE sender='$userid' OR receiver = '$userid' 
Group By sender,receiver 
Order BY time_sent DESC

有解决方案吗？

原文

PHP Mysql Table:Messages

id   sender   receiver      time_sent          Message  SendDel RecDel
1     1          3      2011-08-17 14:00:00     [text]     0      0
2     3          1      2011-08-17 15:00:00     [text]     0      0
3     2          4      2011-08-18 14:19:28     [text]     1      0
4     4          2      2011-08-18 15:19:28     [text]     0      0

Objective is to retrieve the highest value message(MAX) and group the messages based on sender,receiver. So message id's 1 and 2 would group together and id's 3 and 4 would group together.

Example: logged in userid = 2 Should return

id   sender   receiver      time_sent          Message  SendDel RecDel
4     4          2      2011-08-18 15:19:28     [text]     0      0

Im not sure why but my query does not group all the messages together. Here is my query:

SELECT id, sender, receiver, MAX(time_sent), MAX(message) 
FROM Messages
WHERE sender='$userid' OR receiver = '$userid' 
Group By sender,receiver 
Order BY time_sent DESC

Any Solutions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

狼性发作 2024-12-11 07:47:32

你想要：

因此消息 ID 的 1 和 2 将组合在一起，ID 的 3 和 4 将组合在一起。

您必须在 group by 子句中施展一些魔法才能实现这一点。

SELECT id, sender, receiver, MAX(time_sent), MAX(message) 
FROM Messages
WHERE sender='$userid' OR receiver = '$userid' 
Group By (if(sender > receiver,  sender, receiver))
       , (if(sender > receiver,  receiver, sender))
Order BY time_sent DESC

You want:

So message id's 1 and 2 would group together and id's 3 and 4 would group together.

You'll have to do some magic in the group by clause to make this happen.

SELECT id, sender, receiver, MAX(time_sent), MAX(message) 
FROM Messages
WHERE sender='$userid' OR receiver = '$userid' 
Group By (if(sender > receiver,  sender, receiver))
       , (if(sender > receiver,  receiver, sender))
Order BY time_sent DESC

回复收藏 0 原文

~没有更多了~