php - 使用 Group 和 If 语句以及 Order by 选择两个用户之间的最后一条消息

Question

我正在选择聊天系统的两个用户之间的最后一条消息。但是，虽然我可以很好地选择它，但我无法根据未读消息对消息进行排序。

这是我的聊天表的样子

CREATE TABLE IF NOT EXISTS `mychat_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from_id` int(10) unsigned NOT NULL,
  `to_id` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `sent` int(10) unsigned NOT NULL DEFAULT '0',
  `read_statu` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `to` (`to_id`),
  KEY `from` (`from_id`),
  KEY `direction` (`is_deleted`),
  KEY `read` (`read_statu`),
  KEY `sent` (`sent`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

现在选择我这样做的两个用户之间的最后一条消息

$recup_id = mysqli_query($connection, "SELECT MAX(`id`) AS id FROM mychat_table WHERE  (`from_id`='$id' OR `to_id`='$id') AND 

(is_deleted!='$id' AND is_deleted >=0) ORDER BY read_statu ASC GROUP BY (IF(`to_id`='$id', `from_id`, `to_id`))    LIMIT 5  ") or die(mysqli_error($connection));

问题

通常我认为当我执行 ORDER BY read_statu ASC 时，它会首先显示状态为 0 的消息，依此类推，但事实并非如此。

如何通过排序方式显示这些用户之间的消息，首先显示 read_statu=0 的消息，然后再显示 read_statu 优于 0 的消息？

Answer 1

你是对的，ORDER BY read_status ASC 会给你一个按阅读状态排序的列表，但是

1. 你不能在 GROUP BY 子句之前做 ORDER BY
2. 你正在使用 MAX(id) 函数，无论你使用什么顺序，这将返回所选组的最大 id
3. 您正在使用 GROUP BY，这会将结果减少到每个用户只有一个条目，因此您将无法访问所有最后五条消息

要实际获得您想要的结果，您可以简化查询以显示最后五封邮件，并优先显示未读邮件:

SELECT * FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY read_statu ASC, id DESC
LIMIT 5

如果你想要显示的结果整齐，你可以使用这个

SELECT (IF(`to_id`='$id', `from_id`, `to_id`)) AS contact, message, read_statu 
FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY read_statu ASC, id DESC, contact
LIMIT 5

如果您需要对分组而不是 read_status 的偏好，那么只需将联系人放在 ORDER BY 子句的开头:

SELECT (IF(`to_id`='$id', `from_id`, `to_id`)) AS contact, message, read_statu 
FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY contact, read_statu ASC, id DESC
LIMIT 5

我没有你的数据可以玩，所以请以不同的顺序尝试 order 参数，直到你得到想要的结果，这应该会让你到达那里。

如果您只想查看来自每个用户的最后一条消息(最好是未读消息)(每个用户只有一条消息)并且您可以只接受用户 ID 和消息文本(应该足以显示)，那么试试这个:

SELECT DISTINCT from_id, (
    SELECT message FROM mychat_table m2 
    WHERE to_id='$id' AND m2.from_id=m1.from_id
    ORDER BY read_statu ASC, id DESC
    LIMIT 1
) AS message FROM mychat_table m1
WHERE to_id='$id'
LIMIT 5