0

这是一条用于查询用户消息列表的sql,只要显示用户信息,最后一条消息,有三张表

消息阅读表:zx_message 有rec_id(收件人),message_id(消息),status(状态)
消息主题表: zx_meeage_content => sender_id(发送人) content(消息),content_type(消息内容类型) message_type(消息类型)
用户资料表: uid用户关联id user_photo(用户头像) user_name(用户昵称)

我初步完成的sql如下:

SELECT mc.*, u.user_photo,u.user_name FROM (
SELECT _t.*, _t.rec_id as lxr from (SELECT zm.rec_id, zm.message_id, zm.status, zmc.sender_id,zmc.content,zmc.content_type,zmc.message_type,zmc.group_id,zmc.create_at FROM zx_message zm LEFT JOIN zx_message_content as zmc on zmc.id=zm.message_id WHERE (zm.is_deleted = 10) AND (zm.rec_id=10022 OR zmc.sender_id=10022) ORDER BY zmc.create_at DESC) _t WHERE(_t.sender_id=10022) AND (_t.rec_id <> 10022)
UNION
SELECT _t.*, _t.sender_id as lxr from (SELECT zm.rec_id, zm.message_id, zm.status, zmc.sender_id,zmc.content,zmc.content_type,zmc.message_type,zmc.group_id,zmc.create_at FROM zx_message zm LEFT JOIN zx_message_content as zmc on zmc.id=zm.message_id WHERE (zm.is_deleted = 10) AND (zm.rec_id=10022 OR zmc.sender_id=10022) ORDER BY zmc.create_at DESC) _t WHERE(_t.rec_id=10022) AND (_t.sender_id <> 10022)
) mc LEFT JOIN zx_user_detail u ON u.uid = mc.lxr GROUP BY lxr ORDER BY max(mc.create_at) DESC

但是过于冗长,而且用了多次子查询,效率一般,请求高手帮忙优化一条,感谢感谢

0 个回答

撰写答案

推广链接