MySQL选择发件人和收件人之间的最后一次转换消息

Question

我希望通过发件人 ID 或收件人 ID 查询表以获取发件人和收件人组之间的最后一次对话

我有下表

CREATE TABLE `messages` ( 
`message_id` Int( 11 ) AUTO_INCREMENT NOT NULL,
`author_id` Int( 11 ) NOT NULL,
`recipient_id` Int( 11 ) NOT NULL DEFAULT 0,
`message` Text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`date_created` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`date_updated` Timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`recipient_read` Int( 11 ) NOT NULL DEFAULT 0,
`attachment` VarChar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
PRIMARY KEY ( `message_id` ),
CONSTRAINT `message_id` UNIQUE( `message_id` ) )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB;

示例数据:

+------------+-----------+--------------+---------------------------+---------------------+| message_id | author_id | recipient_id |          message          |    date_created     |+------------+-----------+--------------+---------------------------+---------------------+|          1 |         1 |          101 | Hey buddy, what's up!     | 2019-03-08 11:11:07 ||          2 |         1 |          101 | Hope all is well? :)      | 2019-03-08 11:11:30 ||          3 |       101 |            1 | Yes, all good thanks      | 2019-03-08 11:25:04 ||          4 |       101 |            1 | Super busy right now..    | 2019-03-08 11:25:16 ||          5 |         1 |          101 | Same old stuff everyday.  | 2019-03-08 11:26:45 ||          6 |         1 |          101 | Yea I guess so! but lis.. | 2019-03-08 11:27:26 ||          7 |         1 |          100 | Hey man! hit me up, i've  | 2019-03-08 15:43:27 ||          8 |         5 |          101 | Hola! come sta            | 2019-03-08 15:48:13 ||         10 |         1 |           99 | Niky nejez sam            | 2019-03-08 16:06:18 ||         11 |       101 |            1 | Last message!             | 2019-03-08 17:18:44 |+------------+-----------+--------------+---------------------------+---------------------+

我要查询的是特定用户 ID 的最后一条消息，结果应返回与他/她交谈过的所有用户为该用户发送或接收的最后一条消息。

例如，用户 ID 1 的查询应返回以下内容:

+------------+-----------+--------------+--------------------------+---------------------+| message_id | author_id | recipient_id |         message          |    date_created     |+------------+-----------+--------------+--------------------------+---------------------+|          7 |         1 |          100 | Hey man! hit me up, i've | 2019-03-08 15:43:27 ||         10 |         1 |           99 | Niky nejez sam           | 2019-03-08 16:06:18 ||         11 |       101 |            1 | Last message!            | 2019-03-08 17:18:44 |+------------+-----------+--------------+--------------------------+---------------------+

我整天都在绞尽脑汁想弄清楚这个问题，并尝试了各种子查询和分组，但没有任何成功。

谁能指出我正确的方向。

Answer 1

您可以尝试根据最大日期对 session 组的耦合作者使用子查询

SELECT  m.* 
FROM  messages m
INNER JOIN  (
    SELECT IF(author_id <= recipient_id, CONCAT(author_id,'_', recipient_id) ,CONCAT(recipient_id,'_', author_id) ) COUPLE, 
    MAX(date_created) max_date 
    FROM  messages
    GROUP BY COUPLE 
) t ON IF(m.author_id <= m.recipient_id,
         CONCAT(m.author_id,'_', m.recipient_id),
         CONCAT(m.recipient_id,'_', m.author_id) )  = t.COUPLE 
AND t.max_date  = m.date_created