php - MySQL 根据最后发送的消息排序用户列表

Question

我的数据库中有两个表，名为“用户”和“消息”。

用户:

**id** .  **fname**  . **lname**

`1` . `paul` . `jackson`
`2` . `raj` . `krish`
`3` . `john` . `philip`

消息:

**fromId** . **toId** . **sdate** . **txt**


`2` . `1` . `100` . `hi paul`
`2` . `1` . `99` . `hey`
`3` . `1` . `91` . `come on`
`2` . `1` . `56` . `are you there?`
`3` . `1` . `32` . `long time`

我希望能够选择已向 1 发送消息的用户列表，以及从users，并按发送最后一条消息的用户降序排列。

例如:

来自raj krish“嗨保罗”的消息。

来自 john Philip 的消息“加油”。

所以我能够检索向 1 发送消息的用户列表以及每条最后的消息。但问题是，我无法根据最后一条消息的用户对列表进行排序。

我真的是SQL 的新手。这是我试过的代码:

$query = $connection->query("SELECT * FROM users 
    WHERE EXISTS(SELECT NULL FROM message 
    WHERE users.id = message.fromId AND message.toId = 1) 
    ODER BY (SELECT sdate from message 
    ORDER BY sdate DESC 
    LIMIT 1) DESC")

while($usrs = mysqli_fetch_array($query, MYSQLI_BOTH)){
    $mquery = $connection->query("SELECT * FROM message 
        WHERE fromId = " . $usrs["id"] . " AND toId = 1 
        ORDER BY sdate DESC 
        LIMIT 1");

    while($msg = mysqli_fetch_array($mquery, MYSQLI_BOTH){
        ...... Do some return here! .......

    }
}

如何使用每个发送到 1 的最后一条消息对 用户 列表进行排序？

Answer 1

尝试逐步解决此类问题，例如先恢复用户，然后进行完整连接，然后过滤连接结果等...

我会给你一个简单的解决方案，希望对你有所帮助!

v1.0 - 无最终排序

SELECT fname, lname, txt FROM users as u
INNER JOIN
(
SELECT fromId, sdate, txt FROM message GROUP BY fromId ORDER BY sdate desc
) m
ON u.Id = m.fromId

这个 INNER JOIN 非常简单，就像 101 中稍作改动的连接。我没有加入表本身，而是使用您想要加入的合适记录进行子查询。

如果您有任何不清楚的地方，请告诉我。

编辑:

回应您的评论。那不可能是真的。 GROUP BY 会处理它。我复制了您的示例并进行了测试，结果如下:

编辑 2:

你是对的!返回的行不是按最后一条消息时间排序的!您还需要对连接结果进行排序。

v2.0 - 最终排序

SELECT fname, lname, txt, sdate FROM users as u
INNER JOIN
(
SELECT fromId, sdate, txt FROM message GROUP BY fromId ORDER BY sdate desc
) m
ON u.Id = m.fromId
ORDER BY sdate desc

如果您不想在返回的表集中获取 sdate，您可以使用另一个子查询生成结果的子集:

v3.0 - 带有最终排序和投影结果

SELECT fname, lname, txt FROM
(
SELECT fname, lname, txt, sdate FROM users as u
INNER JOIN
(
SELECT fromId, sdate, txt FROM message GROUP BY fromId ORDER BY sdate desc
) m
ON u.Id = m.fromId
ORDER BY sdate desc
) r