我试图在数据库中获取一些对话记录,但只显示最新结果。我试过下面的方法,但它按找到的第一条记录分组。有没有一种方法可以让它按日期列上的最后一条记录分组
SELECT DISTINCT*
FROM table_messages
WHERE fromid=4 OR toid=4
GROUP BY convoid
ORDER BY dateline desc
我的桌子就像下面的
pmid - fromid - toid - convoid - dateline
1 4 15 3 1461079193
2 4 15 3 1461079200
3 15 4 3 1461079220
4 15 4 3 1461079230
5 4 15 3 1461079270
在一个查询中获取特定用户的所有对话列表,以及每个对话发送的最后一条消息(及其详细信息):
SELECT
convo.convoid,
message.pmid,
message.dateline,
CASE WHEN fromid = 4 then "SENT" ELSE "RECEIVED" as direction
FROM
(SELECT convoid, max(dateline) as maxdateline FROM table_messages GROUP BY convoid) convo
INNER JOIN table_messages message
ON convo.convoid = message.convoid AND
convo.maxdateline = message.dateline
WHERE
fromid = 4 or toid = 4
在这里,我们再次使用两套。第一个(来自FROM
)是所有convoid's
及其最大dateline
的列表。然后我们在卷积和日期线的关系上INNER JOIN
那些具有相同表的。这将为我们提供每一次对话的记录集,以及最新的信息。
然后,我们在WHERE
子句中进行限制,只获取fromid
或toid
是您感兴趣的用户的对话。
为了好玩,我添加了CASE
语句,只是为了引入关于最后发送的消息(无论是用户发送的还是用户接收的)的更多信息。
它可能做得更快:
SELECT
convo.convoid,
message.pmid,
message.dateline,
CASE WHEN fromid = 4 then "SENT" ELSE "RECEIVED" as direction
FROM
(SELECT convoid, max(dateline) as maxdateline FROM table_messages GROUP BY convoid WHERE fromid = 4 or toid = 4) convo
INNER JOIN table_messages message
ON convo.convoid = message.convoid AND
convo.maxdateline = message.dateline
取决于MySQL如何优化第一个查询。如果它试图获取每个对话,然后获取最后一条消息,然后为用户限制它,那么您可以强制优化器在这个版本中只获取用户的对话,然后为这些对话获取最新消息。不过,我敢打赌,这是一次清洗,MySQL将正确地优化第一个查询,以执行第二个查询中明确指出的操作。但是我想以防万一,我会把它放在这里。
为了可读性,我更喜欢第一个查询,因为可以很快地看到主查询的WHERE
子句中的条件。否则,您必须在子查询中搜寻。