聊天记录列表SQL

需求，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