>我有一个简单的表格来维护用户之间的消息。表结构如下所示
sender receiver message sendtime
1 2 m1 2012-01-01 12:12:12
2 1 m2 2012-01-01 12:50:20
1 2 m3 2012-01-01 12:55:55
1 3 m4 2012-01-02 05:05:05
1 4 m5 2012-01-05 05:20:20
4 1 m6 2012-01-06 06:05:00
4 1 m7 2012-01-07 11:11:11
2 4 m8 2012-01-08 05:01:01
现在,对于ID 为 1 的用户,我需要这样的结果
sender receiver message sendtime
1 2 m3 2012-01-01 12:55:55
1 3 m4 2012-01-02 05:05:05
4 1 m7 2012-01-07 11:11:11
也就是说,我需要特定用户的最新消息,无论他是发送者还是接收者。
虽然,这似乎很容易,但我找不到编写单个 mysql 查询的方法。
另外,如果对于这种解决方案,我的表格设计很差,我想要建议。
注意:我认为我应该提到的一件事是,例如,用户 1 和用户 2 之间存在多个通信。无论用户 1 是发送方还是接收方,我都需要他们两个之间的最新通信,只有最近的通信。
大多数用户建议查询,这将带来一条记录,发送方 1、接收方 2,然后是发送方 2、接收方 1。我不需要这两个记录,因为这是同一用户 1 和用户 2 之间的通信。我需要为与其他每个用户指定的用户提供一个最新的一个。
谢谢
SELECT data.* FROM
(SELECT MAX(sendtime) AS sendtime
FROM data
WHERE 1 IN (sender,receiver)
GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data ON latest.sendtime = data.sendtime AND 1 IN (data.sender, data.receiver)
GROUP BY IF (1 = data.sender,data.receiver,data.sender)
我建议在表中使用唯一的序列 ID 以避免外部GROUP BY
。如果您有递增的唯一message_id
(即更大的message_id
对应于后面的sendtime
),查询会简单得多:
SELECT data.* FROM
(SELECT MAX(message_id) AS message_id
FROM data
WHERE 1 IN (sender,receiver)
GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data USING(message_id)
尝试将消息作为主键,并将发送方和接收方放在 2 个单独的表中,用于同一消息,如下所示:
Table 1: {message, sender, sendtime}
Table 2: {message, receiver, sendtime}
试试这个:
SELECT Distinct (*)
FROM tableName m
WHERE date =
(
SELECT MAX (date)
FROM tableName
)
,使用基于发送方和接收方的自己的ID感觉更简单。无需JOIN
,只需要一个GROUP BY
:
SELECT sender, receiver, message, MAX(time)
FROM (
SELECT *,
CASE WHEN sender < receiver
THEN CONCAT(sender,'&', receiver)
ELSE CONCAT(receiver,'&', sender) END AS fromto
FROM data) AS a
GROUP BY fromto
我花了 30 分钟来找到这个问题的解决方案,因为我有同样的问题简单来说
SELECT DISTINCT sent_by FROM messages WHERE sent_to = 1 UNION SELECT DISTINCT sent_to FROM messages WHERE sent_by = 1
这将解决
$query = "SELECT * FROM tableName WHERE `sender`='$userNumber' OR `receiver`='$userNumber' ORDER BY `sendtime` DESC LIMIT 0,1";