MySQL获取两个用户之间的对话记录

Question

我是一位有用的助手，可以为您翻译文本。

我有一个名为private_messages的SQL表，其中包含字段（id，from，to，message，stamp）。 stamp字段对应于消息的日期。

那么我需要什么查询来获取两个用户之间的对话（按日期排序）？

我尝试过以下查询：

(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的收件箱）？

- jpceia

Answer 1

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

- John Woo

---

它被称为“别名”。当您有长表名时，它非常有用。 - John Woo

答案已更新。顺便问一下，你有样本记录可以让我们试玩吗？ - John Woo

Answer 2

你能试一下这个吗？

SELECT x.* 
FROM (SELECT * FROM private_messages 
WHERE `to`='$my_id' OR `from`='$my_id' GROUP BY `to`, `from`) AS x 
ORDER BY x.stamp DESC ;

To，From 可能是保留字。请注意，x 是一个表别名。

- bonCodigo

---

@João P. Ceia，请尝试执行上述查询并发表评论。=) - bonCodigo

由于在两个不同的“方向”中返回不同的消息，因此该程序无法按预期工作。 - jpceia

Answer 3

我以前做过这个，但是只用了简单的查询。也许这对你有用。

      SELECT * FROM private_messages WHERE (from=$my_id AND to=$other_id) OR (from=$other_id AND to=$my_id) ORDER BY stamp

- hasnainwasaya

Answer 4

1）我相信你需要在你的PHP变量周围加上引号，像这样：

(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 DESC

2) 尝试类似以下的内容：

SELECT * FROM (SELECT * FROM private_messages WHERE to='$my_id' OR from='$my_id' GROUP BY to, from) AS tmp_table ORDER BY stamp DESC

- Madison Brown

---

在你的回答中，第二点不包括由“我”发送的消息。 - jpceia

查询2）出现错误... SELECT * FROM（SELECT * FROM private_messages WHERE to=1 OR from=1 GROUP BY to, from）AS tmp_table ORDER BY stamp DESC 报错： #1064 - 您的SQL语法有误，请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方法，位于第1行附近的'to, from) AS tmp_table ORDER BY stamp DESC' - jpceia