时间戳

首页 > 解决方案 > MySQL选择发件人和收件人之间的最后一个转换消息

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

问题描述

我正在查询一个表，以通过发件人 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 | 作者_id | 收件人 ID | 留言 | 日期创建 |
+------------+------------+--------------+--------- ------------------+----------+
| 1 | 1 | 101 | 嘿伙计，怎么了！| 2019-03-08 11:11:07 |
| 2 | 1 | 101 | 希望一切安好？:) | 2019-03-08 11:11:30 |
| 3 | 101 | 1 | 是的，很好，谢谢 | 2019-03-08 11:25:04 |
| 4 | 101 | 1 | 现在超级忙.. | 2019-03-08 11:25:16 |
| 5 | 1 | 101 | 每天都一样的旧东西。| 2019-03-08 11:26:45 |
| 6 | 1 | 101 | 是的，我想是的！但李.. | 2019-03-08 11:27:26 |
| 7 | 1 | 100 | 嗨，老兄！打我，我已经 | 2019-03-08 15:43:27 |
| 8 | 5 | 101 | 你好！来吧 | 2019-03-08 15:48:13 |
| 10 | 1 | 99 | Niky nejez sam | 2019-03-08 16:06:18 |
| 11 | 101 | 1 | 最后一条消息！| 2019-03-08 17:18:44 |
+------------+------------+--------------+--------- ------------------+----------+

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

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

+------------+------------+--------------+--------- -----------------+----------+
| message_id | 作者_id | 收件人 ID | 留言 | 日期创建 |
+------------+------------+--------------+--------- -----------------+----------+
| 7 | 1 | 100 | 嗨，老兄！打我，我已经 | 2019-03-08 15:43:27 |
| 10 | 1 | 99 | Niky nejez sam | 2019-03-08 16:06:18 |
| 11 | 101 | 1 | 最后一条消息！| 2019-03-08 17:18:44 |
+------------+------------+--------------+--------- -----------------+----------+

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

谁能指出我正确的方向。

标签： mysqljoinsubquery

解决方案

您可以尝试按最大日期对 convdrsation 组的耦合 autors 使用子查询

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

推荐阅读