mysql - SQL检索每个threadID的最新记录

表结构：

id | sender | receiver | subject | message | threadID | timestamp | date | readStatus

SELECT * FROM mail
WHERE sender = 'redphyre' OR receiver = 'redphyre'
AND threadID BETWEEN (select min(threadID) FROM mail WHERE sender = 'redphyre' OR receiver = 'redphyre' ORDER BY id DESC)
AND (select max(threadID) FROM mail WHERE sender = 'redphyre' OR receiver = 'redphyre' ORDER BY id DESC)
GROUP BY threadID;

我试图让它返回用户redphyre每个单独threadID的最新记录，在这种情况下为threadID的0,1,2。但这不是那样的。取而代之的是，我在threadID之间得到了混合结果。有人有什么想法吗？

最佳答案

想必这是您想要的：

SELECT m.*
FROM mail m
WHERE 'redphyre' IN (m.sender, m.receiver) AND
      m.id = (SELECT MAX(m2.id)
              FROM mail m2
              WHERE 'redphyre' IN (m2.sender, m2.receiver) AND m2.threadID = m.threadID
             );

BETWEEN和GROUP BY确实与您想要的内容无关。子查询考虑同一线程上的所有匹配记录，并返回具有最大id的记录。

关于mysql - SQL检索每个threadID的最新记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52711778/