数据库如何查询好友列表每个好友的最后一条记录

Question

目前表设计是这样的

用户表(user)

好友表(friend)

聊天记录表(chat)

之前没有考虑到最后一条聊天记录，在进入好友列表时，仅仅根据friend表和user表做了一个左连接获取所有好友信息

// 例如查询uid = 10 的好友列表
SELECT
    user .*
FROM
    friend
LEFT JOIN
    USER
ON
    USER.uid = friend.user2
WHERE
    friend.user1 = 10

有没有办法在此基础上通过某种操作把跟每个好友的最后一条聊天记录也一起查到结果集中返回呢

Answer 1

里面有3个10，对应要查询的用户uid

select t1.*,t2.* from
(SELECT
    user .*,
    (select rid from chat where (sender=10 and receiver=user.uid) or (sender=user.uid and receiver=10) order by date desc limit 1) as rid
FROM
    friend
LEFT JOIN
    USER
ON
    USER.uid = friend.user2
WHERE
    friend.user1 = 10
)t1 left join chat t2 on t1.rid=t2.rid

目测这样，可能还有优化空间

Answer 2

select A.member_id,A.friend_id,A.send_time,B.content from
    (
        select A.member_id,A.friend_id,max(B.send_time) as send_time from 
            dd_member_friend A
            left join 
            (
                select A.*,B.* from 
                    dd_member A
                    left join 
                    (select send,receive,max(send_time) as send_time from dd_chat_record
                    GROUP BY send,receive) B on A.id = B.send or A.id = B.receive
                    where A.id=1
            ) B ON (A.member_id = B.send and A.friend_id = B.receive) or (A.member_id = B.receive and A.friend_id = B.send)
            where A.member_id=1
            GROUP BY A.member_id,A.friend_id
    ) A
    left join dd_chat_record B on A.send_time=B.send_time 

遇到一样的问题，
查看了上面一个朋友的回复，他写的sql没用
这个是我的sql语句，表名字段名和你的不同，但结构是一样的，
dd_member表 = user表
dd_member_friend表 = friend表
dd_chat_record表 = chat表
留给后来的朋友参考