mysql - 从每个 "group"中仅选择一个

Question

如果我运行以下 MySQL 命令:

SELECT * FROM mail f where f.toUserId = 15 AND f.toUserDeleted=0 ORDER BY f.sentDate DESC limit 0,100000

我将以正确的顺序获取所有消息，我可以清楚地看到顶部最后发送的最后一条消息。

但是我确实需要像这样结束这个搜索:

SELECT 
   f.id, 
   f.fromUserId, 
   f.fromUserNickName, 
   f.readDate, 
   f.sentDate, 
   f.subject, 
   f.fromUserDeleted, 
   f.toUserId, 
   f.toUserDeleted, 
   (SELECT count(*) FROM mail m1 WHERE m1.fromUserId=f.fromUserId AND m1.toUserId=f.toUserId) as messageCount 
FROM mail f 
WHERE 
   f.toUserId = 15 AND 
   f.toUserDeleted=0 
GROUP BY(f.fromUserId) 
ORDER BY 
   f.sentDate 
DESC 
   limit 0,100000

问题是这个命令会错过上一个 MySQL 命令中显示的一些用户？

编辑 1:

我已经尝试过:

SELECT m1.* 
FROM mail m1 
LEFT JOIN mail m2 ON(m1.fromUserId = m2.fromUserId AND m1.sentDate < m1.sentDate) 
WHERE m2.id IS NULL 
ORDER BY m1.sentDate;

它需要很长时间，不知道它是否真的返回任何东西。

编辑2:

第二次尝试:

SELECT p1.id, p1.fromUserId, p1.fromUserNickName, p1.sentDate
FROM 
    mail p1 
INNER JOIN 
    (SELECT pi.id, MAX(pi.sentDate) as latestDate FROM mail pi GROUP BY pi.fromUserId) p2
    ON(p1.id = p2.id) 
WHERE 
    p1.toUserId = 15 
ORDER BY 
    p1.sentDate desc;

这不会返回所有向用户 15 发送邮件的用户。

编辑3:

Barmar 的第二个建议似乎有效:

SELECT m1.*, (SELECT count(*) FROM mail m3 WHERE m3.fromUserId=m1.fromUserId AND m3.toUserId=m1.toUserId) as messageCount 
FROM mail m1 
LEFT JOIN mail m2 ON (m1.fromUserId = m2.fromUserId 
                      AND m1.sentDate < m2.sentDate 
                      AND m2.toUserId = 15) 
WHERE m2.id IS NULL 
AND m1.toUserId = 15
ORDER BY m1.sentDate DESC;

Answer 1

在这两种情况下，您都不会过滤您加入的表，只过滤发送到您 ID 的消息。

第一种方法:

SELECT m1.* 
FROM mail m1 
LEFT JOIN mail m2 ON (m1.fromUserId = m2.fromUserId 
                      AND m1.sentDate < m2.sentDate 
                      AND m2.toUserId = 15) 
WHERE m2.id IS NULL 
AND m1.toUserId = 15
ORDER BY m1.sentDate DESC;

第二种方法:

SELECT p1.id, p1.fromUserId, p1.fromUserNickName, p1.sentDate
FROM 
    mail p1 
INNER JOIN 
    (SELECT pi.id, MAX(pi.sentDate) as latestDate 
     FROM mail pi 
     WHERE pi.toUserId = 15
     GROUP BY pi.fromUserId) p2
    ON (p1.id = p2.id) 
WHERE 
    p1.toUserId = 15 
ORDER BY 
    p1.sentDate desc;