mySQL组按成员名称 [英] mySQL Group by member name

问题描述

我正在使用此查询来获取向用户发送消息或向用户发送消息的人员的列表.

I am using this query to fetch list of who messaged a user or who user messaged to.

SELECT messages.* 
FROM (
    SELECT MAX(lastseen) AS lastseen 
    FROM messages
    WHERE '".$user."' IN (from,to)
    GROUP BY IF ('".$user."' = from,to,from)
) AS latest
LEFT JOIN messages 
USING(lastseen) 
ORDER BY lastseen desc,(read='no' and to='".$user."') limit 10

此方法有效，但是它向某些用户显示了两次.

This works, however, it shows some users twice.

仅向用户显示一次的正确方法是什么?

What is the correct way to show a user only once ?

推荐答案

我认为这就是您所追求的.

I think this is what you're after.

SELECT messages.* 
  FROM 
  (SELECT MAX(lastseen) AS lastseen,IF ('Tom' = `from`,`to`,`from`) as otheruser FROM messages
    WHERE 'Tom' IN (`from`,`to`) GROUP BY otheruser
   )
AS latest INNER JOIN messages ON latest.lastseen = messages.lastseen
                             AND (('Tom' = messages.from AND latest.otheruser = messages.to)
                                 OR
                                 ('Tom' = messages.to AND latest.otheruser = messages.from))
ORDER BY messages.lastseen
DESC,`read`='no' limit 10

只需将"Tom"替换为您的变量

just replace 'Tom' with your variable

这将返回最近发送消息给'Tom'或'Tom'消息的10个用户.

This will return the latest 10 users that messaged to 'Tom' or 'Tom' messaged to.

这篇关于mySQL组按成员名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！