mySQL查询选择一个字段的最大值与另一个字段的共同点

Question

1我正在尝试编写一个消息表，其中包含像 gmail 这样的对话线程。 (我使用 PHP)。表格如图所示。 posID 表示每条消息在给定 threadID 中的位置。

id | senderID | recvrID |  message(threadID,posID)  | threadID | posID |   time 
1       1          3            msg 1,1                  1          1       12pm
2       3          1            msg 1,2                  1          2        3pm
3       1          2            msg 2,1                  2          1        1pm

我需要编写一个查询来查找所有发送给 senderID=1 的消息线程(在这种情况下)，如果线程中有多个消息，则只选择每个线程中的最后一条消息(可以按 positionID 或排序时间)。

预期输出如下表。

 senderID      |  message                 | Time
   1               msg 1,2                  3pm
   1               msg 2,1                   1pm

编辑:经过更多阅读，我想我需要选择具有发送者/接收者约束的消息，并且(如果线程中有多个消息，则只有每个线程 ID 具有 MAX(posID) 的消息)。不知道如何实现这个。

Answer 1

我认为你的问题具有误导性:

• 您实际上需要所有线程，其中 userID=1 是发送者或接收者；
• 对于 threadID=1 和 posID=2 对应的时间是 3pm，但不是 12pm，因为你'已经指定。

以下查询将按位置为您提供每个线程中的最后一条消息:

SELECT senderID, recvrID, message, time
  FROM message m
  JOIN (SELECT threadID,max(posID) maxPOS FROM message
        GROUP BY threadID) mm
    ON m.threadID = mm.threadID AND m.posID = mm.maxPOS;

您可以添加以下过滤器:

• WHERE senderID=1 用于 userID=1 仅为发件人的邮件。 虽然这与您的示例不匹配；
• WHERE senderID=1 OR recvrID=1 对于所有消息，其中 userID=1 参与。 这将产生预期的结果。

尝试查询 here .

请注意，您的表将无法处理一封邮件有多个收件人的情况。