网上看到的。做个记录。
mysql查询会话列表，每条记录包含以下字段：
未读消息条数，
最后一条消息内容
发送时间
朋友id
朋友昵称
select t.*,m.nickname,m.avatar from
(
SELECT
sum( read_status ) AS unread,
receiver,
type,
content,
send_time
FROM
(
SELECT
to_user_id AS receiver,
type,
content,
0 AS read_status,
send_time
FROM
message
WHERE
from_user_id = 4
AND to_user_id <> 4 UNION
SELECT
from_user_id AS receiver,
type,
content,
read_status,
send_time
FROM
message
WHERE
from_user_id <> 4
AND to_user_id = 4
ORDER BY
send_time DESC
) AS newTable
GROUP BY
receiver
ORDER BY
send_time DESC
LIMIT 30 ) as t INNER JOIN member m ON t.receiver = m.id
另一个版本
SELECT sum(read_status) AS unread,receiver,type,content,send_time,m.nickname,m.avatar
FROM (SELECT to_user_id as receiver,type,content,0 as read_status,send_time FROM message WHERE (from_user_id = $userId) AND (to_user_id <> $userId)
UNION SELECT from_user_id as receiver,type,content,read_status,send_time FROM message WHERE (from_user_id <> $userId) AND (to_user_id = $userId)
ORDER BY send_time DESC) as newTable
INNER JOIN member m on receiver = m.id GROUP BY receiver ORDER BY send_time DESC LIMIT 30
