我有一个名为 private_messages 的 SQL 表,其中包含字段(id、from、to、message、stamp)。
标记字段对应于消息的日期
那么我需要什么查询:
1)获取两个用户之间的对话(按日期排序)?
我已经尝试过查询
(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)获取我和其他用户之间的最后一条消息,每个消息都有一个不同的用户,按日期排序(例如构建一个像 Facebook 中的收件箱)?
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 DESC
2.)
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)