php Mysql一起分组和排序用户消息 [英] php Mysql Grouping and Ordering user messages together

问题描述

PHP Mysql表:消息

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

目标是检索最高价值的消息(MAX)并根据发送者，接收者对消息进行分组.因此，消息ID的1和2将组合在一起，而ID的3和4将组合在一起.

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.

示例:登录的用户ID = 2应返回

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

有解决方案吗?

推荐答案

您要:

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

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

您必须在group by子句中做一些魔术才能做到这一点.

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

这篇关于php Mysql一起分组和排序用户消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！