本文介绍了MySQL在两个用户之间进行对话的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为private_messages的SQL表,带有字段(id,from,to,message,stamp). 标记字段对应于消息的日期
I have a SQL table named private_messages with fields (id,from,to,message,stamp). the stamp field corresponds to the date of the message
那么我需要什么查询:
1)在两个用户之间进行对话(按日期排序)?
1) get a conversation between two users (ordered by date)?
我已经尝试过查询
(SELECT * FROM private_messages WHERE from=$my_id AND to=$other_id) UNION (SELECT * FROM private_messages WHERE from=$other_id AND to=$my_id) ORDER BY stamp ;但不起作用...
2)是否按日期将我和其他用户之间的最后一条消息(每个用户都有不同的用户)按日期排序(例如,像在faceebook中那样构造收件箱)?
2) get the last messages beetween me and other users, each one with a different user, ordered by date (to construct a inbox like in faceebook for example)?
推荐答案1.)
SELECT * FROM private_messages a WHERE (a.from = $my_id AND a.to = $other_id) OR (a.from = $other_id AND a.to = $my_id) ORDER BY stamp DESC2.)
SELECT f.* FROM ( SELECT * FROM private_messages a WHERE (LEAST(a.from, a.to), GREATEST(a.from, a.to), a.stamp) IN ( SELECT LEAST(b.from, b.to) AS x, GREATEST(b.from, b.to) AS y, MAX(b.stamp) AS msg_time FROM private_messages b GROUP BY x, y ) ) f WHERE $my_id IN (f.from, f.to) ORDER BY f.stamp DESC更多推荐
MySQL在两个用户之间进行对话
发布评论