获取用户消息列表的SQL

黄粱一梦2024-03-1310

最近尝试写一个即时应用时候在获取用户的消息列表时候犯了难，以为平常都是写前端的。对数据库也并不是很熟悉(后悔大学时候没有好好学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