php - Mysql 选择不同的列和所有列

Question

我有一个名为 messages 的表，并进行了这样的设置:

`id` is the primary key.
`from` is the current user.
`to` is the receiver of the message.
`by` is the sender of the message.
`date` is the date of the message. its the time() function from php.

我想编写一条语句来选择所有唯一的to用户。另外，我想选择从 from 发送到 to 的最后一条消息，并按 date DESC 排序。我只有 from 变量提供到查询中。我有这段代码，但它只能部分工作。它不会提取两个用户之间的最新消息。

$get_messages = $mysqli->query("SELECT DISTINCT `id`, `from`, `to`, `by`, `date`, `message`, `seen` FROM `messages` GROUP BY `to` WHERE `from` = '$from' ORDER BY `date` DESC");

我在这里做错了什么？请帮忙!

Answer 1

如果我理解正确的话，您需要指定from用户上to用户的最新消息。

SELECT  a.*
FROM    messages a
        INNER JOIN
        (
            SELECT  m.to, MAX(date) max_date
            FROM    messages m
            WHERE   m.from = @from
            GROUP   BY m.to
        ) b ON a.to = b.to
                AND a.date = b.max_date
WHERE   a.from = @from
ORDER   BY a.date DESC