主页 / 问题 / 558397
Accepted
lounah
Asked:2020-08-23 15:28:23 +0000 UTC2020-08-23 15:28:23 +0000 UTC

获取最新记录的 SQL 查询

  • 772

我有一个messages看起来像这样的表：_id (prim key), author, client, time, content.

让它具有以下值：

 _id | author | client | time  | content
--------------------------------------
   1 |  user  |  you   | 21:52 | hi   
   2 |  user  |  you   | 20:43 | wassup   
   3 |  user2 |  you   | 17:45 | привет   
   4 |  user2 |  you   | 18:20 | :(

从这个表中，您需要从每个作者那里得到一条（最后一条）消息，其中client = you.

那些。查询应返回 ID 为 1 和 4（hi和:(）的消息。我怎样才能做到这一点？

mysql

2 个回答

  1. 小提琴

    SELECT t1.*
FROM test t1 INNER JOIN (SELECT author, client, max(time) as mtime
                         FROM test
                         WHERE client = 'you'
                         GROUP BY author, client) t2
     ON t1.author = t2.author AND t1.client = t2.client AND t1.time = t2.mtime
    • 6
  2. Best Answer

    确实，这是错误： 

    SELECT * FROM `table`
WHERE `client` = 'you'
GROUP BY `author`
ORDER BY `time` DESC;

    排序发生在分组之后，需要如下查询：

    SELECT `message`.*
FROM `message`
INNER JOIN
(
    SELECT max(`time`) AS `maxTime`, `author`
    FROM `message`
    GROUP BY `author`
) AS `max`
    ON (`max`.`author` = `message`.`author` // При возможности связать по ID
        AND `max`.`maxTime` = `message`.`time`
        AND `client` = 'you'
    )
ORDER BY `time` DESC;
    • 4

相关问题