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

Question

我有一个看起来像这样的数据库：

我目前使用以下查询来获取特定登录用户的消息列表 (toid)

SELECT 

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 

然后我使用 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 1photo,forename,surname,m.status,datetime,m.type,message,timestampFROM messages m&nbsp;LEFT JOIN users ON users.userID = m.fromidWHERE toid = '$userID' ORDER BY datetime DESC&nbsp;如果您想要多个用户的最新消息，您可能会对按 fromid 分组的消息进行自联接，并获取 fromid 和 max datetime 以过滤消息SELECT&nbsp;photo,forename,surname,m.status,datetime,m.type,message,timestampFROM messages m&nbsp;inner join(&nbsp; &nbsp; SELECT fromid, max(datetime) as lastmessage&nbsp; &nbsp; from messages&nbsp; &nbsp; group by fromid) as filter ON m.fromid = filter.fromidLEFT JOIN users ON users.userID = m.fromidORDER BY surname, forename&nbsp;

Answer 2

您可以使用相关子查询：select m.*from messages mwhere m.timestamp = (select max(m2.timestamp)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from messages m2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where (m.toid, m.fromid) in ( (m2.toid, m2.fromid), (m2.fromid, m2.toid))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; );