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已收到消息但尚未发送消息的任何消息
因此,如果用户1向用户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

到目前为止我试过但不太奏效的是:
//这将为每个项目获取最新消息,但不按用户分开
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,如果是,则会在他的屏幕上发布一条“You need to reply”消息。

最佳答案

如果您给出了一些涵盖大多数案例的示例输出,这可能会有所帮助。从你的评论来看,在重读了几遍之后,我想这就是你想要的:

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

10-08 13:38
查看更多