如何在我的数据库查询中对所有对话（消息）进行分组

Question

我有一个看起来像这样的数据库：我目前使用以下查询来获取特定登录用户的消息列表 (toid)SELECT photo,forename,surname,m.status,datetime,m.type,message,timestampFROM messages m LEFT JOIN users ON users.userID = m.fromidWHERE toid = '$userID' ORDER BY datetime DESC 然后我使用 foreach($messages as $m)：然后回显一些向用户显示消息的代码。当前，如屏幕截图所示，有来自相同 fromid 和 toid 的消息，并且它当前会打印这两条消息。我想知道有没有办法将它们分组并只将其显示为一条消息，但显示最近收到的一条？我的 php 循环代码 <?foreach ($messages as $m):?><li <?if ($m->status == "0"){?> class="unread" <?}?>><a href="dashboard-messages-conversation.html"><div class="message-avatar"><img src="https://process.filestackapi.com/resize=width:960,height:960,fit:crop/<?echo $m->photo;?>" alt="" /></div><div class="message-by"><div class="message-by-headline"><h5><?echo $m->forename . " " . $m->surname;?> <?if ($m->status == "0"){?> <i>Unread</i><?}?></h5><span><? time_stamp($m->timestamp);?></span></div><p><?echo truncate($m->message, 100);?></p></div></a></li><?endforeach;?>

Answer 1

如果您只想要最新的，因为您是为单个用户选择消息，您需要将 TOP 子句添加到您的选择语句中。

SELECT TOP 1

photo,forename,surname,m.status,datetime,m.type,message,timestamp

FROM messages m 

LEFT JOIN users ON users.userID = m.fromid

WHERE toid = '$userID' ORDER BY datetime DESC 

如果您想要多个用户的最新消息，您可能会对按 fromid 分组的消息进行自联接，并获取 fromid 和 max datetime 以过滤消息

SELECT 

photo,forename,surname,m.status,datetime,m.type,message,timestamp

FROM messages m 

inner join

(

    SELECT fromid, max(datetime) as lastmessage

    from messages

    group by fromid

) as filter ON m.fromid = filter.fromid

LEFT JOIN users ON users.userID = m.fromid

ORDER BY surname, forename 

Answer 2

您可以使用相关子查询：

select m.*

from messages m

where m.timestamp = (select max(m2.timestamp)

                     from messages m2

                     where (m.toid, m.fromid) in ( (m2.toid, m2.fromid), (m2.fromid, m2.toid))

                    );