获取聊天列表及最后一条消息

表结构

 

 

 查询sql

 SELECT
	t.*,
	user_info.*,
	`user`.*
FROM
	(
	SELECT
		receiver as from_id,
		type,
		message,
		create_time,
               #统计未读消息数量
		sum(IF(tip_flag=0,1,0)) AS num
	FROM
		(
             #tip_flag为提醒标志，1为不提醒，我发出的消息不做提醒
		SELECT
			to_id AS receiver,
			type,
			message,
			create_time,
			1 AS tip_flag
		FROM
			message 
		WHERE
			from_id = #{id}
		 UNION
               #别人发给我的消息，tip_flag取原值
		SELECT
			from_id AS receiver,
			type,
			message,
			create_time,
			tip_flag  
		FROM
			message 
		WHERE
			to_id = #{id}
		ORDER BY
			create_time DESC 
		) AS newTable 
	GROUP BY
		receiver 
	ORDER BY
		create_time DESC 
	) AS t
        #关联其他表并展示信息
	INNER JOIN user_info ON t.from_id = user_info.user_id
	INNER JOIN `user` ON t.from_id = `user`.id