MySQL:显示前 5 个父行的最后一个子行(没有重复的父行)

Question

我正在尝试按用户获取最后 5 条有回复的消息。查询结果中不能重复用户:

SELECT
message.id_message AS id_message, message.message AS message,
user.id_user AS id_user, user.name AS name, user.lastname AS lastname
FROM user
    JOIN message ON message.id_user = user.id_user
    JOIN reply ON reply.id_message = message.id_message
WHERE
    user.flg_ban IS NULL
    AND
    message.id_message IN (SELECT MAX(id_message) FROM message GROUP BY id_user)
    AND
    reply.id_reply IS NOT NULL
GROUP BY user.id_user
ORDER BY message.id_message DESC LIMIT 5

结果应该是:

• 1. 斯凯勒:这是给斯凯勒的唯一信息
• 1. 杰西:给杰西的第三条信息
• 1. 沃尔特:给沃尔特的第二条信息

出于某种原因，我只得到一条记录。

结构的 fiddle :http://sqlfiddle.com/#!9/1f114b/1

Answer 1

嗯。 . .

SELECT m.id_message, m.message, u.id_user, u.name, u.lastname, u.facebook_id AS facebook_id
FROM user u JOIN
     message m
     ON m.id_user = u.id_user
WHERE u.flg_ban IS NULL AND
      EXISTS (SELECT 1 FROM reply r WHERE r.id_message = m.id_message) AND
      m.id_message >= COALESCE((SELECT m2.id_message
                                FROM message m2
                                WHERE m2.id_user = m.id_user AND
                                      EXISTS (SELECT 1 FROM reply r2 WHERE r2.id_message = m2.id_message)
                                ORDER BY m2.id_message DESC
                                LIMIT 4, 1
                               ), 0
                              );

请注意，表别名使查询更易于编写和阅读。

我认为这是对您想要的内容的非常直接的翻译。唯一的小怪癖是使用 LIMIT 4, 1 来获得第五大 id_message。 MySQL 不支持带有相关子查询的 IN，因此这是一个方便的解决方法。