获取用户消息列表的SQL

最近尝试写一个即时应用时候 在获取用户的消息列表时候犯了难，以为平常都是写前端的。对数据库也并不是很熟悉(后悔大学时候没有好好学sql呜呜)。
翻遍了全网 终于找到了一篇博客 之后慢慢摸索也算写出来了

主要是两张表 一张用户表 一张消息表
用户表(tb_user)

聊天表(tb_chat_record)

SQL语句如下 供借鉴(大佬手下留情是真不会sql啊 能写出来我已经很高兴了，轻轻蹂躏我)

SELECT * from tb_user AS us INNER JOIN
(SELECT lxr,send_user_account,message_count,create_time,receipt_user_account,message_status,message_type,has_delete FROM (
		SELECT d.receipt_user_account as lxr,d.* from tb_chat_record d 
    	WHERE (d.send_user_account='YY_VdM9jBXyz') AND (d.receipt_user_account = 'YY_VdM9jBXyz') 
   	UNION
		SELECT e.send_user_account as lxr,e.* from tb_chat_record e
		where(e.send_user_account<>'YY_VdM9jBXyz') AND (e.receipt_user_account = 'YY_VdM9jBXyz')
		ORDER BY create_time DESC
) AS f WHERE message_status = 0
GROUP BY lxr,send_user_account,message_count,create_time,receipt_user_account,message_type,has_delete
ORDER BY MAX(create_time) DESC)AS mes WHERE us.user_account= mes.lxr

---

查询结果

顺便粘贴一下借鉴的地方 链接https://blog.csdn.net/bingshuining/article/details/25489359