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

Question

表结构:

id |发件人 |接收器 |主题 |留言 |线程ID |时间戳|日期 |读取状态

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;

我试图让它为每个单独的 threadID 返回用户 redphyre 的最新记录，在本例中为 threadID 的 0,1,2。但它不是那样工作的。相反，我得到了 threadID 之间的混合结果。大家有什么想法吗？

Answer 1

想必，这就是您想要的:

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 的记录。