是否对具有多个最大值的消息进行分组

Question

我在论坛中有一个私人消息系统。我想重建这个消息传递系统，使其不再基于消息，而是基于对话。它应该像Whatsapp或任何其他信使一样，你没有消息，只有联系人。

我已经编写了以下SQL查询。

SELECT t.*, 
p.root_level, 
p.message_time, 
p.icon_id, 
p.to_address, 
p.message_attachment, 
p.bcc_address, 
u.username, 
u.username_clean, 
u.user_colour, 
p.message_reported, 
Max(p.msg_id) AS maxid, 
p.message_subject 
FROM   phpbb3_privmsgs_to t 
LEFT JOIN phpbb3_privmsgs p 
ON ( p.msg_id = t.msg_id ) 
LEFT JOIN phpbb3_users u 
ON ( u.user_id = p.author_id ) 
WHERE  t.user_id = 1234 
AND t.user_id != p.author_id 
AND t.pm_deleted = 0 
GROUP  BY t.author_id, 
t.user_id 
ORDER  BY maxid DESC

此查询运行良好。分组和排序是有效的，但每个组中的message_time不是最新的。记录最旧对话的时间/日期。

如何在每个群中获取最新的message_time？

Answer 1

这是一个非常常见的"获取具有最大值"的行的细节；要求在您的情况下，您希望为每个作者获取privmsgs表中的最新消息。然后您想将其加入到您的users表中。

首先，使用子查询来查找每个作者(用户(的最新消息时间。像这样：

SELECT author_id,  MAX(message_time) message_time
FROM privmsgs
GROUP BY author_id

用手测试。请注意，它只为每个author_id值提供一行，其中一列显示最新的message_time。您需要message_time来选择需要从哪一行获取详细信息。

让我们这样做吧。接下来，将该子查询合并到查询中，如下所示。

SELECT u.user_id, u.username, 
p.*, whatever, but no MAX() functions
FROM users u
JOIN privmsgs p ON u.user_id = p.author_id
JOIN (
SELECT author_id,  MAX(message_time) message_time
FROM privmsgs
GROUP BY author_id
) maxmsgt    ON p.author_id = maxmsgt.author_id
AND p.message_time = maxmsgt.message_time
WHERE (whatever filter)
ORDER BY (whatever order)

JOINing这个子查询允许您给出一个ON条件，只获取最新时间的消息。GROUP BY操作在子查询中，而不是在主查询中。它看起来很复杂，但却是一种非常常见的查询模式。