我想做手机聊天软件的同学经常会遇到这么一个问题,设计一个专门显示消息记录的view,就像QQ一样,显示与所登录账号有过聊天的最新一条记录,那么这个时候SQL语句该怎么写呢。
我尝试过许多种写法,今天终于成功实现,这要感谢百度知道里khg305387543 同学的提示,msg表里有字段fromU,toU,message,date等,可以用下面的sql语句查询出,同学们可以用用试试哦。
SELECT * FROM (
SELECT toU as lxr, * FROM msg WHERE
(fromU = '所登录的账号') AND (toU <> '所登录的账号')
UNION
SELECT fromU as lxr,* FROM msg WHERE (fromU <> '所登录的账号') AND (toU = '所登录的账号')
) GROUP BY lxr ORDER BY max(date) DESC;