MySQL之a表连b表两次返回第一条数据

遇到问题

私信表和用户表

私信表中有from_id和to_id，a和b之间的会话有同一个conversion_id

查询时，需要查询用户a和其他用户的最近一条记录，并查出from_id和to_id对应的avatar和nickname

实现

select 
	message.*,
    a.avatar as from_avatar,
    a.nickname as from_nickname,
	b.avatar as to_avatar,
	b.nickname as to_nickname
from message
	LEFT JOIN `user` a ON a.user_id = message.from_id
	LEFT JOIN `user` b ON b.user_id = message.to_id
where id in(
    select max(id) from message 
    where status != 2 
    and from_id != 1 
    and (from_id = 'user_id' or to_id = 'user_id')
    group by conversion_id
)
order by id desc
limit 0, 10