CREATE TEMPORARY TABLE IF NOT EXISTS t1 AS
(SELECT message_id, message_from_uid, message_to_uid, message_time
FROM private_msgs
WHERE message_from_uid=$UID)
UNION
(SELECT message_id, message_to_uid, message_from_uid, message_time
FROM private_msgs
WHERE message_to_uid=$UID);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 AS SELECT * FROM t1;
SELECT m.*
FROM (t1 LEFT OUTER JOIN t2
ON (t1.message_from_uid=t2.message_from_uid AND t1.message_to_uid=t2.message_to_uid AND t1.message_time<t2.message_time))
JOIN private_msgs m ON t1.message_id=m.message_id
WHERE t2.message_id IS NULL AND t1.message_from_uid=$UID
ORDER BY t1.message_time DESC;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
SELECT m1.*
FROM private_msgs m1 LEFT OUTER JOIN private_msgs m2
ON (m1.message_from_uid=m2.message_from_uid AND m1.message_to_uid=m2.message_to_uid AND m1.message_time<m2.message_time)
WHERE m2.message_id IS NULL AND m1.message_from_uid=$uid
ORDER BY message_time DESC;
select m.*
from private_messages m
join user_table s ON m.sender_id = s.id
join user_table r ON m.receiver_id = r.id
join (select if(sender_id = $UID, receiver_id, sender_id) as user_id_other,
max(posted_at) AS date_time_max from private_messages
where (sender_id = $UID OR receiver_id = $UID)
group by if(sender_id=$UID, receiver_id, sender_id)
) AS t
on if(m.sender_id = $UID, m.receiver_id, m.sender_id) = user_id_other
AND m.posted_at=date_time_max
where m.sender_id = $UID OR m.receiver_id = $UID
order by m.posted_at desc
select m.*
from YOUR_MESSAGES_TABLE m
--- OPTIONAL: join YOUR_USER_TABLE s ON m.message_from_uid = s.id
--- OPTIONAL: join YOUR_USER_TABLE r ON m.message_to_uid = r.id
join (select if(message_from_uid = $YOUR_USER_ID, message_to_uid, message_from_uid) as user_id_other,
max(message_time) AS date_time_max from YOUR_MESSAGES_TABLE
where (message_from_uid = $YOUR_USER_ID OR message_to_uid = $YOUR_USER_ID)
group by if(message_from_uid=$YOUR_USER_ID, message_to_uid, message_from_uid)
) AS t
on if(m.message_from_uid = $UID, m.message_to_uid, m.message_from_uid) = user_id_other
AND m.message_time=date_time_max
where m.message_from_uid = $UID OR m.message_to_uid = $UID
order by m.message_time desc limit 5
3条答案
按热度按时间xe55xuns1#
在这里,又长又丑。您可以在一个大的final查询中将临时表替换为子查询。最后是解释。
t1临时表接受$uid为发送方的所有消息,并将它们与$uid为接收方的消息合并,但将$uid作为发送方。在t1中,谁是发送者/接收者并不重要。重要的是这些是$uid和其他人之间的对话。
t2是t1的副本,因为在mysql中不能将临时表与其自身连接起来。
最后我们进行查询。我们加入t1和t2,在每个会话中只获取最新的消息,并将其从最新到最旧排序。在t1和t2中,发送方和接收方是混合的,我们使用私有消息进行最后一次连接以获得原始数据。
对不起,我的英语不是以英语为母语的。
wb1gzix02#
不是很优雅但很实用:
加上限制5或任何你想要的
编辑。抱歉,我没有注意到uid可以作为发送者或接收者。您可以发布带有示例数据的表吗?例如:http://sqlfiddle.com?
无论如何,这应该是关键:
lvjbypge3#
在我的旧代码中找到:
对结果不负责,但这会有所帮助。变量本身必须被替换,我没有对代码进行太多修改,如您所见。但是这片。。。代码。。。工作。
你的情况是:
测试:http://sqlfiddle.com/#!9/f66fd8/4层