需要一个mysql查询来解决“group by”问题和“排序依据”不返回最新消息

Question

CREATE TABLE `messages` (
  `message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `message_project_id` int(7) unsigned NOT NULL DEFAULT '0',
  `message_time` int(11) unsigned NOT NULL DEFAULT '0',
  `message_from_user_id` int(7) unsigned NOT NULL DEFAULT '0',
  `message_to_user_id` int(7) unsigned NOT NULL DEFAULT '0',
  `message_details` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`message_id`)
)


CREATE TABLE `project` (
  `project_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `project_user_id` int(7) unsigned NOT NULL DEFAULT '0',
  `project_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `project_status` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`project_id`)
) 

我想要检索的是每个项目给用户 #2 的最新消息。

用户 #2 是该项目的所有者，因此可以接收来自许多感兴趣方的消息。

实际页面将显示用户#2 的“待办事项”列表。我想查找当前打开的项目的任何消息，其中用户 #2 已收到消息，但尚未发送消息，

消息表中将会有一行

message_id | project_id | message_time | message_from_user_id | message_to_user_id | message_details
30 | 12 | 1304707966 | 1 | 2 | Hello user number two, thank you for your interest in my project
31 | 12 | 1304707970 | 2 | 1 | Hello user number one, Your project looks interesting
32 | 12 | 1304707975 | 3 | 1 | Hello user number one, here is my first message, im user number three.  I want to do your project
32 | 13 | 1304707975 | 7 | 1 | Hello user number one, here is my first message, im user number seven.  I want to do your other project

因此如果用户 #1 向用户 #2 发送消息， 到目前为止已尝试过但不太有效： //这将为我提供每个项目的最新消息，但不会按用户分开

SELECT cur.*, p.*
FROM messages cur
LEFT JOIN messages next ON cur.message_project_id = next.message_project_id AND cur.message_time < next.message_time
LEFT JOIN project p ON p.project_id = cur.message_project_id
WHERE next.message_time IS NULL
AND (cur.message_from_user_id = 2 OR cur.message_to_user_id = 2)
AND (p.project_status LIKE 'open' OR p.project_status LIKE 'started')
AND p.project_user_id = 2
ORDER BY cur.message_time DESC 

//这将按用户分开，但不会返回最新的消息文本

SELECT *
FROM messages m
LEFT JOIN project p ON p.project_id = m.message_project_id
WHERE (message_from_user_id = 2 OR message_to_user_id = 2 )
AND (p.project_status LIKE 'open' OR p.project_status LIKE 'started')
AND p.project_user_id = 2
GROUP BY project_id
ORDER BY message_time DESC 

一旦数据返回到 Php，我会检查是否是最新的消息是给用户 #2 的，如果是，则将“您需要回复”消息发布到他的屏幕上。

Answer 1

如果您提供了一些涵盖大多数情况的示例输出，可能会有所帮助。从您的评论来看，并且在重读几次之后，我认为这就是您正在寻找的内容：

SELECT
    <column list>
FROM
    Messages M
INNER JOIN Projects P ON
    P.project_id = M.message_project_id AND
    P.project_status IN ('open', 'started') AND
    P.project_user_id = 2
WHERE
    M.message_to_user_id = 2 AND
    NOT EXISTS (
        SELECT *
        FROM Messages M2
        WHERE
            M2.message_from_user_id = 2 AND
            M2.message_project_id = M.message_project_id AND
            M2.message_to_user_id = M.message_from_user_id AND
            M2.message_time >= M.message_time
    )

这将为您提供用户尚未获得的所有项目的所有消息向该项目的发件人发送了一条消息。当然，您可以添加 ORDER BY 。

Answer 2

这个有用吗：

SELECT *
FROM messages m
LEFT JOIN project p ON (p.project_id = m.message_project_id)
WHERE (m.message_from_user_id = 211 OR m.message_to_user_id = 211 )
AND p.project_status IN('open','started') AND p.project_user_id = 2
GROUP BY p.project_id
ORDER BY m.message_time DESC 

希望有帮助