需求,A用户聊天记录列表,最后一条消息,筛选删除过的消息
表:
CREATE TABLE `ml_hxim_msg` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`chat_type` varchar(16) DEFAULT NULL COMMENT 'chat: 单聊;groupchat: 群聊',
`from` varchar(255) DEFAULT NULL COMMENT '发送人',
`to` varchar(255) DEFAULT NULL COMMENT '接受人',
`data` text COMMENT '聊天数据',
`send_time` int(11) DEFAULT NULL,
`recall` tinyint(1) unsigned DEFAULT '0' COMMENT '1撤回',
`del1` tinyint(1) unsigned DEFAULT '0' COMMENT 'id大的用户,1删除',
`del2` tinyint(1) unsigned DEFAULT '0' COMMENT 'id小的用户,1删除',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SQL:
SELECT
`chat_type`,
`from`,
`to`,
`data`,
`send_time`,
`nickname`,
`avatar`,
`sex`
FROM
(
SELECT #id倒序最后一次聊天记录
*
FROM
ml_hxim_msg
WHERE
send_time IN (
SELECT #查询最后一次聊天记录
MAX( send_time )
FROM
ml_hxim_msg
WHERE #筛选未删记录的最后一条
(`from` = 'A用户' AND IF(`from` > `to`, `del1`, `del2`) = 0)
OR
(`to` = 'A用户' AND IF(`from` > `to`, `del2`, `del1`) = 0)
GROUP BY #通过id拼接去重
CONCAT( #连接字符串
IF
( `from` > `to`, `from`, `to` ),
IF
( `from` < `to`, `from`, `to` )
)
)
AND ( `from` = 'A用户' OR `to` = 'A用户' )
ORDER BY
id DESC
) AS c
LEFT JOIN ml_user AS u ON c.`from` = u.id #联查聊天对象
OR c.`to` = u.id
WHERE
u.id != 'A用户'
GROUP BY
c.send_time DESC