mysql - 在 SQL 中创建消息列表

Question

我正在尝试创建一个消息列表，例如 Facebook。

1. 仅显示用户对话中的最后一条消息(历史记录)
2. 当我发送时显示我的，当我收到答复时显示已答复的消息(1 行)

数据库数据:

SQL查询

SELECT m.message_id, u.username, m.subject, m.message
     , m.status, UNIX_TIMESTAMP(m.date) as date
  FROM users u
  LEFT JOIN messages m ON m.sender_id = u.id
 WHERE m.message_id in (SELECT max(msg.message_id) 
                          FROM messages msg 
                         WHERE msg.receiver_id = 3 
                         GROUP BY sender_id)
 UNION
SELECT m.message_id, u.username, m.subject, m.message
     , m.status, UNIX_TIMESTAMP(m.date) as date
  FROM users u
  LEFT JOIN messages m ON m.receiver_id = u.id
 WHERE m.message_id in (SELECT max(msg.message_id)  
                          FROM messages msg 
                         WHERE msg.sender_id = 3 
                         GROUP BY receiver_id)
 GROUP BY u.username
 ORDER BY date DESC

我尝试接收我发送的所有消息(我的 id = 3)以及所有发送给我和按用户名分组的消息

SQL结果:

Array
(
    [0] => Array
        (
            [message_id] => 10
            [username] => 8888
            [subject] => без темы
            [message] => 555
            [status] => 0
            [date] => 11 August 2012, 2:22
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [1] => Array
        (
            [message_id] => 7
            [username] => 8888
            [subject] => hi
            [message] => 333
            [status] => 0
            [date] => 11 August 2012, 2:15
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [2] => Array
        (
            [message_id] => 4
            [username] => 6666
            [subject] => Тема
            [message] => 2
            [status] => 0
            [date] => 11 August 2012, 2:02
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [3] => Array
        (
            [message_id] => 1
            [username] => fffffffff
            [subject] => privet
            [message] => tttt
            [status] => 0
            [date] => 11 August 2012, 1:38
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

)

如您所见，GROUP BY 用户名不起作用。它显示 3->7 和 7->3，但 7->3 是答案和最后一条消息。我不知道为什么组不起作用。也许你可以帮助我用更简单的方法来解决这个问题？

因此，SQL 必须具有“sender_id = 3”、“receiver_id = 3”，并且我的表数据结果必须是

• message_id -> 1
• message_id -> 4
• message_id -> 10

Answer 1

我认为您的查询正在产生“正确”的结果，就好像您想查看某些对话中的最后一条消息一样，您应该按 conversation_id 进行分组。不过，我在架构中没有看到这个字段。

如果您执行WHERE sender_id = 3 GROUP BY receive_id，那么它是正确的，该查询会返回消息 1 和 7，因为这些消息已发送至不同的 人，因此在你的设计中他们是不同的对话。

如果您只想查看您发送的最后一条消息，只需删除 UNION 第二部分中的 GROUP BY 即可。否则，请考虑重新设计您的架构。

<小时/>

编辑:

尝试这个查询:

SELECT m.message_id, u.username, m.subject, m.message,
       m.status, UNIX_TIMESTAMP(m.date) as `date`
  FROM users u
  LEFT JOIN messages m ON m.sender_id = u.id
 WHERE m.message_id IN (
    SELECT max(message_id) 
      FROM messages
     WHERE receiver_id = 3 OR sender_id = 3
     GROUP BY least(sender_id,receiver_id), 
              greatest(sender_id,receiver_id)
    );

一些注意事项:

1. 不再需要UNION；
2. 这种方法会将两方之间的所有电子邮件视为一次对话，但这并不总是正确的。您可能想要重新设计此方法；
3. 使用保留字(例如date)作为列名或别名是一种不好的风格，请尽量避免这种情况。或者如果确实使用反引号，也可以使用它们。